pgroonga

pig build get pgroonga; # get pgroonga source code
pig build dep pgroonga; # install build dependencies
pig build pkg pgroonga; # build extension rpm or deb
pig build ext pgroonga; # build extension rpms

pig repo add pgsql -u   # add PGDG + Pigsty repo and update cache (leave existing repos)

pig ext install pgroonga; # install by extension name, for the current active PG version
pig ext install pgroonga; # install via package alias, for the active PG version
pig ext install pgroonga -v 18;   # install for PG 18
pig ext install pgroonga -v 17;   # install for PG 17
pig ext install pgroonga -v 16;   # install for PG 16
pig ext install pgroonga -v 15;   # install for PG 15
pig ext install pgroonga -v 14;   # install for PG 14
pig ext install pgroonga -v 13;   # install for PG 13

CREATE EXTENSION pgroonga;

CREATE EXTENSION IF NOT EXISTS pgroonga;

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.');

SET enable_seqscan = off;

-- now let's query pgroonga

SELECT * FROM memos WHERE content &@ 'engine';
--  id |                                content                                 
-- ----+------------------------------------------------------------------------
--   2 | Groonga is a fast full text search engine that supports all languages.
-- (1 row)

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)

SELECT * FROM memos WHERE content LIKE '%engine%';
--  id |                                content                                 
-- ----+------------------------------------------------------------------------
--   2 | Groonga is a fast full text search engine that supports all languages.
-- (1 row)