DEV Community

Dan Croak
Dan Croak

Posted on • Updated on • Originally published at dancroak.com

Postgres \set variable

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;
Enter fullscreen mode Exit fullscreen mode

I like to run scripts from Vim through psql with this config.

Discussion (0)