Full-Text Search

CALL pgsearch.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'mock_items'
);

CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, category, rating, in_stock, created_at, metadata)
WITH (key_field='id');

SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes';

SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'keyboard' OR category @@@ 'toy';

-- Using @@@ operator for numeric comparison
SELECT id, description, rating
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ '>3'
ORDER BY id;

-- Using standard SQL operators
SELECT id, description, rating
FROM mock_items
WHERE description @@@ 'shoes' AND rating > 3;

-- Exact match
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ '4';

-- Date filtering
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND created_at @@@ '"2023-04-20T16:38:02Z"';

-- Boolean filtering
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND in_stock @@@ 'true';

-- Range queries
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ '[1 TO 4]';

-- Term range
SELECT description, rating, category
FROM mock_items
WHERE description @@@ '[book TO camera]';

-- Set filtering
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ 'IN [2 3 4]';

SELECT description, rating, category
FROM mock_items
WHERE description @@@ '"ergonomic keyboard"~1';

       description        | rating |  category   
--------------------------+--------+-------------
 Ergonomic metal keyboard |      4 | Electronics
(1 row)

SELECT id, pgsearch.snippet(description)
FROM mock_items
WHERE description @@@ 'shoes'
LIMIT 5;

SELECT description, rating, category, pgsearch.score(id) AS score
FROM mock_items
WHERE description @@@ 'shoes'
ORDER BY score DESC
LIMIT 5;

SELECT id, pgsearch.score(id) AS score
FROM mock_items
WHERE description @@@ 'shoes^2' OR category @@@ 'footwear'
ORDER BY score DESC
LIMIT 5;

-- Simple match
SELECT description, rating, category
FROM mock_items
WHERE id @@@ pgsearch.match('description', 'running shoes');

-- JSON-based query
SELECT description, rating, category
FROM mock_items
WHERE id @@@ '{
  "match": {
    "field": "description",
    "value": "running shoes"
  }
}'::jsonb;

-- Custom tokenizer
SELECT description, rating, category
FROM mock_items
WHERE id @@@ pgsearch.match(
  'description',
  'running shoes',
  tokenizer => pgsearch.tokenizer('whitespace')
);