Skip to content

Instantly share code, notes, and snippets.

@leandrosilva
Created October 6, 2022 20:47
Show Gist options
  • Save leandrosilva/242c60d270a26c1eb04f01b4279feb0d to your computer and use it in GitHub Desktop.
Save leandrosilva/242c60d270a26c1eb04f01b4279feb0d to your computer and use it in GitHub Desktop.
Full Text Search "sort of" with prefix, infix, fuzzy, and similarity on the Almighty Postgres
SELECT
pos,
sim,
p.*
FROM
produtos AS p,
NULLIF(POSITION('seme' IN LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, ''))), 0) AS pos,
NULLIF(SIMILARITY(LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, '')), 'seme'), 0) AS sim
WHERE
(LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, ''))) LIKE 'seme%'
OR (LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, ''))) LIKE '%seme%'
OR 'seme' % ANY(STRING_TO_ARRAY(LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, '')), ' '))
AND (pos > 0 OR sim > 0)
ORDER BY
pos ASC NULLS LAST, sim DESC NULLS LAST
LIMIT 10;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment