Search code examples
postgresqlvariablespsql

How set variable as date type in psql?

to_date can convert string into date:

select  to_date('2024-01-01','YYYY-MM-DD');
  to_date   
------------
 2024-01-01
(1 row)

Let's check the data type:

select  pg_typeof(to_date('2024-01-01','YYYY-MM-DD'));
 pg_typeof 
-----------
 date

Why can't set an variable as date type?

\set mydate to_date('2024-01-01','YYYY-MM-DD')
select :mydate;
ERROR:  column "yyyy" does not exist
LINE 1: select to_date(2024-01-01,YYYY-MM-DD);
                                  ^
Time: 0.212 ms
Solution

  • The \set command is not part of PostgreSQL itself, but a feature of the psql command-line utility. The relevant section in the documentation includes this:

    psql provides variable substitution features similar to common Unix command shells. Variables are simply name/value pairs, where the value can be any string of any length.

    So, there is no such thing as a "date variable" within this system. The meta-command \set mydate to_date('2024-01-01','YYYY-MM-DD') assigns a string value to the variable named mydate. When you then use :mydate, that string is simply inserted verbatim at that point in the SQL to be run.

    You might think that would still be fine, because the result would be to run this:

    select to_date('2024-01-01','YYYY-MM-DD');

    But what actually happens is that the single quotes are treated specially by the \set meta-command, so that what ends up in the variable is to_date(2024-01-01, YYYY-MM-DD), and the SQL you're trying to run is this:

    select to_date(2024-01-01,YYYY-MM-DD);

    This explains the error - 2024-01-01 is a valid expression, a funny way of writing 2022; but YYYY without any quotes around looks like a column name.

    So, if you really want to use the string interpolation feature in this case, you need to escape the quotes. I haven't tested, but I think that should look like this:

    \set mydate 'to_date(\'2024-01-01\',\'YYYY-MM-DD\')'

    However, remember that this is not storing the date in the variable, it's just saving a string to "copy-and-paste" into future queries.

    select :mydate;

    Now becomes:

    select to_date('2024-01-01','YYYY-MM-DD');

    As pointed out by Laurenz Albe, you can also use \gset to store the result of a query into a variable:

    select to_date('2024-01-01','YYYY-MM-DD') AS mydate \gset

    Again, though, it's just saving a string, so the above is useless: it's the same as \set mydate '2024-01-01'. It might be useful if your actual value is more dynamic, though:

    select CURRENT_DATE AS mydate \gset

    This results in mydate containing the string representation of today's date, without any quotes around it. To use it in queries, you need to use the :'variable_name' syntax, so that it is correctly quoted in the query:

    select :'mydate' as test;

    This results in something like:

    select '2024-01-30' as test;