Tristan Rice

Software Engineer - ML Infra, Modeling, Security

Crazy Postgres Queries

2 Minutes 300 Words

I’ve been working on implementing search for documents. I’m not sure if I’m every going to implement search for body content, but I thought I should probably implement it for titles & users.

It turns out that PostgreSQL has pretty nice full text search support with lexemes. I’ve been following this article pretty closely:

The only issue I’ve encountered is that it doesn’t do direct text matching. For example if you have a title ‘Bananas are tasty!’ and you search for ‘ban’ it won’t match. To work around this I combined full text search with an non case sensitive pattern match. My query is getting kind of long, but it seems to work well.

Here’s the full thing:

SELECT id, name, permissions.user_email
FROM (SELECT as id, as name, || ' ' ||
  coalesce((string_agg(p1.user_email, ' ')), '') || ' ' ||
  regexp_replace(coalesce((string_agg(p1.user_email, ' ')), ''), '[@.+]', ' ', 'g') as text,
  to_tsvector( ||
  to_tsvector(coalesce((string_agg(p1.user_email, ' ')), '')) ||
  to_tsvector(regexp_replace(coalesce((string_agg(p1.user_email, ' ')), ''), '[@.+]', ' ', 'g'))
  as document
  FROM ws_files
  JOIN permissions p1
  ON p1.file_id =
  JOIN permissions p2
  ON p2.file_id =
  WHERE p2.user_email=''
  GROUP BY f_search
JOIN permissions
ON permissions.file_id = id
WHERE permissions.level = 'owner'
AND ((f_search.document @@ to_tsquery('')) OR
  f_search.text ILIKE ('%' || '' || '%'));

This matches all documents that ‘’ can access and have the phrase ‘’ in their title or emails.

  id  |       name       |     user_email
 1936 | Unnamed Document |