By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Date variables in psql

P: n/a
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?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Francisco Reyes <li***@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.