&^~> operator

Since 1.0.9.

Summary

This operator uses v2 operator class. It doesn't provide backward compatibility until PGroonga 2.0.0. Use it carefully.

&^~> operator performs prefix RK search. R is for Romaji. K is for Kana.

Prefix RK search is useful for Japanese.

Prefix RK search is useful for implementing input completion.

Syntax

column &^~> prefix

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

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

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

The operator returns true when one of the column values start with prefix.

Usage

Here are sample schema and data for examples:

CREATE TABLE tags (
  name text PRIMARY KEY,
  readings text[]
);

CREATE INDEX pgroonga_tags_index ON tags
  USING pgroonga (readings pgroonga.text_array_term_search_ops_v2);
INSERT INTO tags VALUES ('PostgreSQL',
                         ARRAY['ポストグレスキューエル', 'ポスグレ', 'ピージー']);
INSERT INTO tags VALUES ('Groonga',    ARRAY['グルンガ']);
INSERT INTO tags VALUES ('PGroonga',   ARRAY['ピージールンガ']);
INSERT INTO tags VALUES ('pglogical',  ARRAY['ピージーロジカル']);

You can perform prefix RK search with prefix in Romaji by &^~> operator:

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

You can also use Hiragana for prefix:

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

You can also use Katakana for prefix:

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

See also