&^~| operator

Since 1.2.1.

Summary

&^~| operator performs prefix RK search by an array of prefixes. R is for Romaji. K is for Katakana. If one ore more prefixes are matched, the record is matched.

Prefix RK search is useful for Japanese.

Prefix RK search is useful for implementing input completion.

Syntax

column &^~| prefixes

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

prefixes is an array of prefixes to be found. It's text[] type.

column values must be in Katakana. prefixes must be in Romaji, Hiragana or Katakana.

The operator returns true when the column value starts with one or more prefixes in prefixes.

Operator classes

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

• pgroonga_text_term_search_ops_v2: For text

• pgroonga_text_array_term_search_ops_v2: For text[]

• pgroonga_varchar_term_search_ops_v2: For varchar

Usage

Here are sample schema and data for examples:

CREATE TABLE tags (
  name text PRIMARY KEY,
  reading text
);

CREATE INDEX pgroonga_tags_index ON tags
  USING pgroonga (reading 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 RK search with prefixes in Romaji by &^~| operator:

SELECT * FROM tags WHERE reading &^~| ARRAY['pi-ji-', 'posu'];
--     name    |        reading         
-- ------------+------------------------
--  PostgreSQL | ポストグレスキューエル
--  PGroonga   | ピージールンガ
--  pglogical  | ピージーロジカル
-- (3 rows)

You can also use Hiragana for prefixes:

SELECT * FROM tags WHERE reading &^~| ARRAY['ぴーじー', 'ぽす'];
--     name    |        reading         
-- ------------+------------------------
--  PostgreSQL | ポストグレスキューエル
--  PGroonga   | ピージールンガ
--  pglogical  | ピージーロジカル
-- (3 rows)

You can also use Katakana for prefixes:

SELECT * FROM tags WHERE reading &^~| ARRAY['ピージー', 'ポス'];
--     name    |        reading         
-- ------------+------------------------
--  PostgreSQL | ポストグレスキューエル
--  PGroonga   | ピージールンガ
--  pglogical  | ピージーロジカル
-- (3 rows)

See also

• &^ operator: Prefix search

• &^~ operator: Prefix RK search

• &^| operator: Prefix search by an array of prefixes

• !&^| operator: NOT prefix search by an array of prefixes