QziYK4wk

1CREATE extension if not exists pg_trgm;
2	
3CREATE TABLE tbl (
4    col_text  text,
5    col_json  jsonb
6);
7
8CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_text gin_trgm_ops);
9	
10SELECT * FROM tbl WHERE col_text LIKE '%foo%';   -- leading wildcard
11SELECT * FROM tbl WHERE col_text ILIKE '%foo%';  -- works case insensitive as well
12	
13CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_json gin_trgm_ops);
14	
15ERROR (datatype_mismatch): operator class "gin_trgm_ops" does not accept data type jsonb
16	
17CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_json->>company gin_trgm_ops);
18	
19ERROR (syntax_error): syntax error at or near "->>"
20	
21CREATE INDEX table_col_trgm_idx ON tbl USING gin ((col_json->>'company') gin_trgm_ops);
22	
23SELECT * FROM tbl WHERE col_json->>'company' LIKE '%foo%';