Postgres Pro
Downloads
Home   >   mailing lists

Thread: psql syntax for array of strings in a variable?

psql syntax for array of strings in a variable?

From
Philip Semanchuk
Date:
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
ratherthan repeat those strings over and over in the script, I'd like to declare them once at the top of the script and
thenrefer 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

Re: psql syntax for array of strings in a variable?

From
"David G. Johnston"
Date:
On Friday, October 29, 2021, Philip Semanchuk <philip@americanefficient.com> wrote:
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']

Not sure why the single quotes are getting stripped out but that is the issue.  Maybe double them up to escape them like in a normal text literal?

Otherwise consider just doing a comma delimited string and using string_to_array in the sql to turn it into an array at runtime.
 


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

Not with \set.  You could write sql to output the text and then execute it using \gexec to store the column value in a variable.  That too may be a solution to the missing single quotes.

David J.
 

Re: psql syntax for array of strings in a variable?

From
Pavel Stehule
Date:
Hi

pá 29. 10. 2021 v 19:21 odesílatel Philip Semanchuk <philip@americanefficient.com> napsal:
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

psql variables can hold only text. There is not any type - all is just text.

Regards

Pavel

Re: psql syntax for array of strings in a variable?

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, October 29, 2021, Philip Semanchuk <philip@americanefficient.com>
> wrote:
>> 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']

> Not sure why the single quotes are getting stripped out but that is the
> issue.  Maybe double them up to escape them like in a normal text literal?

Yeah, that's just the way that \set works (and most other psql backslash
commands, I believe).  You've likely got an issue with whitespace
disappearing, too, though that might be harmless in this specific example.

regression=# \set foo 'bar baz'
regression=# \echo :foo
bar baz
regression=# \set foo 'bar ''baz'
regression=# \echo :foo
bar 'baz
regression=# \set foo bar ''baz 
regression=# \echo :foo
barbaz

Not sure offhand how well-documented this is.

            regards, tom lane

Re: psql syntax for array of strings in a variable?

From
Philip Semanchuk
Date:

> On Oct 29, 2021, at 2:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Friday, October 29, 2021, Philip Semanchuk <philip@americanefficient.com>
>> wrote:
>>> 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']
>
>> Not sure why the single quotes are getting stripped out but that is the
>> issue.  Maybe double them up to escape them like in a normal text literal?
>
> Yeah, that's just the way that \set works (and most other psql backslash
> commands, I believe).  You've likely got an issue with whitespace
> disappearing, too, though that might be harmless in this specific example.
>
> regression=# \set foo 'bar baz'
> regression=# \echo :foo
> bar baz
> regression=# \set foo 'bar ''baz'
> regression=# \echo :foo
> bar 'baz
> regression=# \set foo bar ''baz
> regression=# \echo :foo
> barbaz
>
> Not sure offhand how well-documented this is.

Thanks, all. Glad to know I wasn’t missing something obvious.

> On Oct 29, 2021, at 1:52 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

> psql variables can hold only text. There is not any type - all is just text.


^^^ This was especially helpful; I’d never considered that before.

Cheers
Philip