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

&^ operator

Since 1.2.1.

Summary

&^> operator for text[] is deprecated since 1.2.1. Use &^ operator instead.

&^ operator performs prefix search.

Prefix search is useful for implementing input completion.

Syntax

column &^ prefix
column &^ (prefix, NULL, index_name)::pgroonga_full_text_search_condition
column &^ pgroonga_condition(prefix, index_name => 'index_name')

The first signature is enough for most cases.

The second signature is for using custom normalizer even if PGroonga's index is used or not. The second signature is available since 2.4.6.

The third signature has the same meaning as the second signature. You should use this signature in 3.1.6 and above. See pgroonga_condition function for details.

Here is the description of the first signature.

column &^ prefix

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

prefix is a prefix to be found. It's text type.

The operator returns true when the column value starts with prefix.

Here is the description of the second signature.

column &^ (prefix, NULL, index_name)::pgroonga_full_text_search_condition

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

prefix is a prefix to be found. It's text type.

The second argument is set only NULL. Because this syntax is not for optimizing search score.

index_name is an index name of the corresponding PGroonga index. It's text type.

It's for using the same search options specified in PGroonga index in sequential search.

Operator classes

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

Usage

Here are sample schema and data for examples:

CREATE TABLE tags (
  name text PRIMARY KEY
);

CREATE INDEX pgroonga_tag_name_index ON tags
  USING pgroonga (name pgroonga_text_term_search_ops_v2);

INSERT INTO tags VALUES ('PostgreSQL');
INSERT INTO tags VALUES ('Groonga');
INSERT INTO tags VALUES ('PGroonga');
INSERT INTO tags VALUES ('pglogical');

You can perform prefix search with prefix by &^ operator:

SELECT * FROM tags WHERE name &^ 'pg';
--    name    
-- -----------
--  PGroonga
--  pglogical
-- (2 rows)

You can use custom normalizer in prefix search as below.

CREATE TABLE tags (
  name text
);

CREATE INDEX pgroonga_tag_name_index ON tags
  USING pgroonga (name pgroonga_text_term_search_ops_v2)
  WITH (normalizers='NormalizerNFKC150("remove_symbol", true)');

INSERT INTO tags VALUES ('PostgreSQL');
INSERT INTO tags VALUES ('Groonga');
INSERT INTO tags VALUES ('PGroonga');
INSERT INTO tags VALUES ('pglogical');

You can prefix search with custom normalizer even if PGroonga's index is not used.

SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;

EXPLAIN (COSTS OFF)
SELECT name
  FROM tags
 WHERE name &^ ('-p_G', NULL, 'pgroonga_tag_name_index')::pgroonga_full_text_search_condition;
QUERY PLAN
Seq Scan on tags
  Filter: (name &^ '(-p_G,,pgroonga_tag_name_index)'::pgroonga_full_text_search_condition)
(2 rows)

SELECT name
  FROM tags
 WHERE name &^ ('-p_G', NULL, 'pgroonga_tag_name_index')::pgroonga_full_text_search_condition;
   name    
-----------
 PGroonga
 pglogical
(2 rows)

The same query with pgroonga_condition function is as follows.

EXPLAIN (COSTS OFF)
SELECT name
  FROM tags
 WHERE name &^ pgroonga_condition('-p_G', index_name => 'pgroonga_tag_name_index');
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Seq Scan on tags
   Filter: (name &^ '(-p_G,,,,pgroonga_tag_name_index,,)'::pgroonga_condition)
(2 rows)

SELECT name
  FROM tags
 WHERE name &^ pgroonga_condition('-p_G', index_name => 'pgroonga_tag_name_index');
   name
-----------
 PGroonga
 pglogical
(2 rows)

See also