This is a document for PGroonga 2.X and 3.X. See PGroonga 1.x document when you're using old PGroonga.

`@@` operator for non `jsonb` types

This operator is deprecated since 1.2.0. Use &@* operator instead.

Summary

@@ operator performs full text search with query.

Query's syntax is similar to syntax that is used in Web search engine. For example, you can use OR search by KEYWORD1 OR KEYWORD2 in query. You can use AND search by KEYWORD1 KEYWORD2 in query.

Syntax

column @@ query

column is a column to be searched. It's text type, text[] type or varchar type.

query is a query for full text search. It's text type for text type or text[] type column. It's varchar type for varchar type column.

Groonga's query syntax is used in query.

Operator classes

You need to specify one of the following operator classes to use this operator:

pgroonga_text_full_text_search_ops_v2: Default for text
pgroonga_text_array_full_text_search_ops_v2: Default for text[]
pgroonga_varchar_full_text_search_ops_v2: For varchar
pgroonga_text_full_text_search_ops: For text
pgroonga_text_array_full_text_search_ops: For text[]
pgroonga_varchar_full_text_search_ops: For varchar

Usage

Here are sample schema and data for examples:

CREATE TABLE memos (
  id integer,
  content text
);

CREATE INDEX pgroonga_content_index ON memos USING pgroonga (content);

INSERT INTO memos VALUES (1, 'PostgreSQL is a relational database management system.');
INSERT INTO memos VALUES (2, 'Groonga is a fast full text search engine that supports all languages.');
INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga as index.');
INSERT INTO memos VALUES (4, 'There is groonga command.');

You can perform full text search with multiple keywords by @@ operator like KEYWORD1 KEYWORD2. You can also do OR search by KEYWORD1 OR KEYWORD2:

SELECT * FROM memos WHERE content @@ 'PGroonga OR PostgreSQL';
--  id |                            content                             
-- ----+----------------------------------------------------------------
--   3 | PGroonga is a PostgreSQL extension that uses Groonga as index.
--   1 | PostgreSQL is a relational database management system.
-- (2 rows)

See Groonga document for query syntax details.

Note that you can't use syntax that starts with COLUMN_NAME: like COLUMN_NAME:@KEYWORD. It's disabled in PGroonga.

You can't use COLUMN_NAME:^VALUE for prefix search. You need to use VALUE* for prefix search.

Sequential scan

TODO: Describe about SET search_path = "$user",public,pgroonga,pg_catalog;.

@@ operator for non jsonb types