Dan Croak

Posted on Aug 4, 2020 • Updated on Dec 6, 2020 • Originally published at dancroak.com

Postgres \set variable

#postgres #vim

Within psql, you can \set variables and reference them with :'var-name'. For example:

\set query '%SaaS%'

SELECT
  'https://example.com/companies/' || companies.id AS url,
  companies.name
FROM
  companies
  JOIN notes ON notes.company_id = companies.id
WHERE
  companies.name ILIKE :'query'
  OR companies.description ILIKE :'query'
  OR notes.comments ILIKE :'query'
GROUP BY
  url,
  companies.name
ORDER BY
  companies.name ASC;