Date variables in psql
Trying the following simple sql file:
\set proc_date 6/30/2004
\echo Date is :proc_date
select * from feeds where date = :proc_date limit 20;
If I start psql with the "-a" option I see the output:
\set proc_date 6/30/2004
\echo Date is :proc_date
Date is 6/30/2004
select * from feeds where date = :proc_date limit 20;
and get no records
If I type the exact same query manually it workds
select * from feeds where date = '6/30/2004' limit 20;
Any ideas of the variable may not be recognized on the select statement?
Francisco Reyes <lists@natserv.com> writes:
Trying the following simple sql file:
\set proc_date 6/30/2004
\echo Date is :proc_date
select * from feeds where date = :proc_date limit 20;
That's going to expand to
select * from feeds where date = 6/30/2004 limit 20;
whereas what you need is
select * from feeds where date = '6/30/2004' limit 20;
It's fairly painful to get single quotes into a psql variable;
AFAIK you have to do it like this:
\set proc_date '\'6/30/2004\''
regards, tom lane
On Thu, 7 Oct 2004, Tom Lane wrote:
It's fairly painful to get single quotes into a psql variable;
AFAIK you have to do it like this:
\set proc_date '\'6/30/2004\''
Thanks that worked.
I figure I needed to escape the single quotes, but I had tried
\'6/30/2004\', which did not work.