Обсуждение: Is `DATE` a function?

Hello, Friends,

Use it like a normal function:

playground=# SELECT DATE();
ERROR:  function date() does not exist
LINE 1: SELECT DATE();
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

playground=# SELECT DATE('2022-01-13');
    date
------------
 2022-01-13
(1 row)

Another reason I think this is a function is that other types don't
seem to have the same behavior:

playground=# SELECT integer('123');
ERROR:  syntax error at or near "("
LINE 1: SELECT integer('123');
                      ^

The Table 9.33. Date/Time Functions in the documentation
(https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-TABLE)
doesn't mention this...
If I missed it, where should I find the description? Or maybe it
actually should be in this table?

Thanks in advance.

On Sat, 7 Oct 2023 21:01:59 +0800
jinser <aimer@purejs.icu> wrote:

Hi,

> Hello, Friends,
>
> Use it like a normal function:
>
> playground=# SELECT DATE();
> ERROR:  function date() does not exist
> LINE 1: SELECT DATE();
>                ^
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
>
> playground=# SELECT DATE('2022-01-13');
>     date
> ------------
>  2022-01-13
> (1 row)
>
> Another reason I think this is a function is that other types don't
> seem to have the same behavior:
>
> playground=# SELECT integer('123');
> ERROR:  syntax error at or near "("
> LINE 1: SELECT integer('123');
>                       ^
>
> The Table 9.33. Date/Time Functions in the documentation
> (https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-TABLE)
> doesn't mention this...
> If I missed it, where should I find the description? Or maybe it
> actually should be in this table?

Is is both a function and an operator, but the function has an arity of
one (text, representing a date, timestamp, etc), hence the error message
you've got.
If you just want to get today's date, use : SELECT current_date;
or if you want to twist your mind : SELECT date(now());

Jean-Yves

jinser <aimer@purejs.icu> writes:
> playground=# SELECT DATE('2022-01-13');
>     date
> ------------
>  2022-01-13
> (1 row)

Sure, there are functions named date():

postgres=# \df date
                             List of functions
   Schema   | Name | Result data type |     Argument data types     | Type
------------+------+------------------+-----------------------------+------
 pg_catalog | date | date             | timestamp with time zone    | func
 pg_catalog | date | date             | timestamp without time zone | func
(2 rows)

The reason these aren't explicitly documented is that they are intended as
implementation support for casts.

postgres=# \dC date
                                      List of casts
         Source type         |         Target type         |  Function   |   Implicit?
-----------------------------+-----------------------------+-------------+---------------
 date                        | timestamp with time zone    | timestamptz | yes
 date                        | timestamp without time zone | timestamp   | yes
 timestamp with time zone    | date                        | date        | in assignment
 timestamp without time zone | date                        | date        | in assignment
(4 rows)

Hence, the preferred spelling is more like

    select now()::date;

or if you want to be SQL-spec-compatible,

    select cast(now() as date);

but for historical reasons we like to let you also write

    select date(now());

which is managed (in most cases) by naming cast implementation
functions the same as the target type.

> Another reason I think this is a function is that other types don't
> seem to have the same behavior:

> playground=# SELECT integer('123');
> ERROR:  syntax error at or near "("

You're running into a couple of things there: INTEGER is a reserved
word, and the cast functions for that type are named after the
internal type name "int4".

postgres=# \dC integer
                              List of casts
   Source type    |   Target type    |      Function      |   Implicit?
------------------+------------------+--------------------+---------------
 "char"           | integer          | int4               | no
 bigint           | integer          | int4               | in assignment
 bit              | integer          | int4               | no
 boolean          | integer          | int4               | no
 double precision | integer          | int4               | in assignment
 integer          | "char"           | char               | no
 ...

postgres=# select int4('123');
 int4
------
  123
(1 row)

Note that none of these have anything to do with the syntax for
a typed literal, which is "type-name quoted-literal" with no
parentheses:

postgres=# select date 'today';
    date
------------
 2023-10-07
(1 row)

postgres=# select integer '42';
 int4
------
   42
(1 row)

Some aspects of the behavior might look the same, but there
are a lot of edge cases.

            regards, tom lane

Thank you so much for your explanation.
With the reminder of ”cast function“, I found a more detailed
explanation in the document that I missed before:
https://www.postgresql.org/docs/16/sql-expressions.html#SQL-SYNTAX-TYPE
-CASTS.

Thanks again everyone :)

Tom Lane <tgl@sss.pgh.pa.us> 于2023年10月7日周六 22:38写道：
>
> jinser <aimer@purejs.icu> writes:
> > playground=# SELECT DATE('2022-01-13');
> >     date
> > ------------
> >  2022-01-13
> > (1 row)
>
> Sure, there are functions named date():
>
> postgres=# \df date
>                              List of functions
>    Schema   | Name | Result data type |     Argument data types     | Type
> ------------+------+------------------+-----------------------------+------
>  pg_catalog | date | date             | timestamp with time zone    | func
>  pg_catalog | date | date             | timestamp without time zone | func
> (2 rows)
>
> The reason these aren't explicitly documented is that they are intended as
> implementation support for casts.
>
> postgres=# \dC date
>                                       List of casts
>          Source type         |         Target type         |  Function   |   Implicit?
> -----------------------------+-----------------------------+-------------+---------------
>  date                        | timestamp with time zone    | timestamptz | yes
>  date                        | timestamp without time zone | timestamp   | yes
>  timestamp with time zone    | date                        | date        | in assignment
>  timestamp without time zone | date                        | date        | in assignment
> (4 rows)
>
> Hence, the preferred spelling is more like
>
>         select now()::date;
>
> or if you want to be SQL-spec-compatible,
>
>         select cast(now() as date);
>
> but for historical reasons we like to let you also write
>
>         select date(now());
>
> which is managed (in most cases) by naming cast implementation
> functions the same as the target type.
>
> > Another reason I think this is a function is that other types don't
> > seem to have the same behavior:
>
> > playground=# SELECT integer('123');
> > ERROR:  syntax error at or near "("
>
> You're running into a couple of things there: INTEGER is a reserved
> word, and the cast functions for that type are named after the
> internal type name "int4".
>
> postgres=# \dC integer
>                               List of casts
>    Source type    |   Target type    |      Function      |   Implicit?
> ------------------+------------------+--------------------+---------------
>  "char"           | integer          | int4               | no
>  bigint           | integer          | int4               | in assignment
>  bit              | integer          | int4               | no
>  boolean          | integer          | int4               | no
>  double precision | integer          | int4               | in assignment
>  integer          | "char"           | char               | no
>  ...
>
> postgres=# select int4('123');
>  int4
> ------
>   123
> (1 row)
>
> Note that none of these have anything to do with the syntax for
> a typed literal, which is "type-name quoted-literal" with no
> parentheses:
>
> postgres=# select date 'today';
>     date
> ------------
>  2023-10-07
> (1 row)
>
> postgres=# select integer '42';
>  int4
> ------
>    42
> (1 row)
>
> Some aspects of the behavior might look the same, but there
> are a lot of edge cases.
>
>                         regards, tom lane
>