Thread: Default for date field: today vs CURRENT_DATE

   Reading the manual I saw that 'today' is a special value, but it did not
work when I used it as a column default; e.g.,
start_date date DEFAULT today,

   Appending parentheses also failed. But, changing today to CURRENT_DATE
worked. I've not found an explanation and would appreciate learning why
'today' fails.

TIA,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:
>    Reading the manual I saw that 'today' is a special value, but it did not
> work when I used it as a column default; e.g.,
> start_date date DEFAULT today,
>    Appending parentheses also failed. But, changing today to CURRENT_DATE
> worked. I've not found an explanation and would appreciate learning why
> 'today' fails.

'today' is special as a date input string, so you can use it as a literal:

regression=# select 'today'::date;
    date    
------------
 2019-01-02
(1 row)

But it's not a SQL keyword, nor a function name, so you can't write it
without quotes.

Also, it wouldn't be very useful for this purpose, because it's resolved
on sight in date_in().  Thus

regression=# create table wrong_thing (start_date date DEFAULT 'today');
CREATE TABLE
regression=# \d wrong_thing
                  Table "public.wrong_thing"
   Column   | Type | Collation | Nullable |      Default       
------------+------+-----------+----------+--------------------
 start_date | date |           |          | '2019-01-02'::date

The default would effectively be the creation date of the table,
not the insertion date of any particular row.

So CURRENT_DATE or one of its sibling functions is what you want
here.  On the other hand, something like

INSERT INTO my_table VALUES ('today', ...);

might be perfectly sensible code.

            regards, tom lane

On Wed, 2 Jan 2019, Tom Lane wrote:

> 'today' is special as a date input string, so you can use it as a literal:
>
> regression=# select 'today'::date;
>    date
> ------------
> 2019-01-02
> (1 row)
>
> But it's not a SQL keyword, nor a function name, so you can't write it
> without quotes.

Tom,

   Now I understand. I tried it with quotes, too, but this was in the table
definition, not as a selection criterion. I did not pick up this difference
when reading about the date-time data types.

   Thanks very much for clarifying.

Best regards,

Rich