storing an arbitrary date in a variable in Postgres

Question

[This is probably really easy, but I've googled unsuccessfully for far too long]

I want to store a series of dates in variables in Postgres in order to run a series of queries against them:

\set SESSION startDate  date'2018-04-01'
\set SESSION reportDate  CURRENT_DATE

I then want to run a series of queries like

SELECT COUNT(id) FROM assets
WHERE target_date > :startDate
AND target_date < :reportDate;

My variable "reportDate" works fine, but can someone please tell me how to correctly set an arbitrary date as "startDate". I've tried all manners of escaping the value for the \set command unsuccessfully.

The error I get is:

ERROR:  column "date2018" does not exist

I'm using PostgreSQL 9.3.22.

Many Thanks

Try changing \set SESSION startDate date'2018-04-01' to \set SESSION startDate '2018-04-01' or possibly \set SESSION startDate "'2018-04-01'" — 404
– 404, Commented Apr 27, 2018 at 16:10

Abelisto · Accepted Answer · 2018-04-27 18:52:49Z

You should to double single quotes to use it in psql variables:

postgres=# \set foo 'date ''2018-04-01'''
postgres=# select :foo;
┌────────────┐
│    date    │
├────────────┤
│ 2018-04-01 │
└────────────┘

Note about single quotes around whole value.

You could to get the exact variable value using :'<variable name>':

postgres=# \set foo1 date'2018-01-01'
postgres=# \set foo2 'date ''2018-01-01'''
postgres=# select :'foo1', :'foo2';
┌────────────────┬───────────────────┐
│    ?column?    │     ?column?      │
├────────────────┼───────────────────┤
│ date2018-01-01 │ date '2018-01-01' │
└────────────────┴───────────────────┘

Or just by \set metacommand without parameters.

Kristianmitk · Accepted Answer · 2018-04-27 14:00:29Z

1

Try to_date method to convert a string to a date.

Example:

to_date('2018-04-01','YYYY-MM-DD')

edited Apr 27, 2018 at 14:00

Kristianmitk

4,7985 gold badges30 silver badges50 bronze badges

answered Apr 27, 2018 at 11:52

Petar Srdanovic

193 bronze badges

1 Comment

LukeW Over a year ago

Thanks for replying Petar, Can you give an example of how to set that into a variable for me? I can't get either of these to work: \set SESSION startDate to_date('2018-04-01','YYYY-MM-DD') \set SESSION startDate $$to_date('2018-04-01','YYYY-MM-DD')$$

J. Alderete · Accepted Answer · 2018-04-27 17:22:30Z

1

I actually never used it, but I think you can SET your startDate like this:

SET SESSION myvariables.startDate = '2018-04-01';

And then get it's value like this when needed:

SELECT current_setting('myvariables.startDate')::date;

The "::date" it's because the variable is stored as text and you want a date.

Also, if you do it this way, you can change the value of that variable/parameter by using this function:

set_config(setting_name, new_value, is_local);

Example:

select set_config('myvariables.startDate', '20170101', 'no');

Now 'myvariables.startDate' value should be '20170101'.

Hope this helps with your problem.

edited Apr 27, 2018 at 17:22

answered Apr 27, 2018 at 16:47

J. Alderete

1161 silver badge4 bronze badges

Collectives™ on Stack Overflow

storing an arbitrary date in a variable in Postgres

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related