psql syntax for array of strings in a variable?

From: Philip Semanchuk <philip(at)americanefficient(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: psql syntax for array of strings in a variable?
Date: 2021-10-29 17:21:19
Message-ID: 8A98A8A6-7AFE-42C8-884B-984323381269@americanefficient.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I would appreciate help with the syntax for querying an array of strings declared as a psql variable. Here's an example.

\set important_days ARRAY['monday', 'friday']

select 1 where 'monday' = ANY(:important_days);
ERROR: 42703: column "monday" does not exist
LINE 1: select 1 where 'monday' = ANY(ARRAY[monday,friday]);

select 1 where 'monday' = ANY(:"important_days");
ERROR: 42703: column "ARRAY[monday,friday]" does not exist
LINE 1: select 1 where 'monday' = ANY("ARRAY[monday,friday]");

I'm doing something wrong but I can't figure out what.

My real-world use case is that I have a psql script that will execute several queries on a long list of strings, and rather than repeat those strings over and over in the script, I'd like to declare them once at the top of the script and then refer to the variable after that. Bonus points if there's a way to do a multiline declaration like --

\set important_days ARRAY['monday',
'friday']

Thanks for reading
Philip

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Serov 2021-10-29 17:22:56 Question: Is it possible to get the new xlog position after query execution?
Previous Message Adrian Klaver 2021-10-29 15:24:47 Re: DBeaver does not show all tables in a Schema