Get started with TIN

CREATE EXTENSION IF NOT EXISTS tin;

ALTER EXTENSION tin UPDATE;

CREATE TABLE posts (
  id bigint PRIMARY KEY,
  category text NOT NULL,
  body text NOT NULL
);

INSERT INTO posts (id, category, body) VALUES
  (1, 'fruit', 'I love fuji apples and juicy mangoes'),
  (2, 'tasting', 'Grape tasting notes from the orchard'),
  (3, 'fruit', 'The best juicy fuji apple in town');

CREATE INDEX posts_body_tin ON posts USING tin (body);

SELECT * FROM tin.tokenize('Jalapeño 😀');
SELECT * FROM tin.tokenize('Jalapeño 😀', accent_folding => 'preserve');
SELECT * FROM tin.tokenize('runs running runner', stemmer => 'en');

SELECT id, body
FROM posts
WHERE body ==> 'apple AND "fuji apple"';

SELECT id, tin.score(ctid) AS score, body
FROM posts
WHERE body ==> 'apple OR grape'
ORDER BY score DESC, id
LIMIT 10;

SELECT id,
       tin.score(ctid) AS score,
       tin.score(ctid) / tin.max_score(ctid) AS relative
FROM posts
WHERE body ==> 'apple OR grape'
ORDER BY score DESC
LIMIT 10;

SELECT count(*)
FROM posts
WHERE body ==> 'juicy';

SELECT id, tin.highlight(body)
FROM posts
WHERE body ==> 'apple'
LIMIT 20;

SELECT tin.highlight(body, '<mark>', '</mark>', 'apple')
FROM posts
WHERE id = 1;

CREATE TABLE fruits (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  notes text NOT NULL
);

CREATE INDEX fruits_name_tin ON fruits USING tin (name);
CREATE INDEX fruits_notes_tin ON fruits USING tin (notes);

SELECT id, tin.score(ctid) AS score, name, notes
FROM fruits
WHERE name ==> 'fuji^1.5'
  AND notes ==> 'citrus'
ORDER BY score DESC
LIMIT 10;