Thread

1. Is `DATE` a function?

   jinser <aimer@purejs.icu> — 2023-10-07T13:01:59Z

   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.
   
   
   
   

2. Re: Is `DATE` a function?

   Bzzzz <lazyvirus@gmx.com> — 2023-10-07T14:28:23Z

   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
   
   
   
   

3. Re: Is `DATE` a function?

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-10-07T14:38:30Z

   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
   
   
   
   

4. Re: Is `DATE` a function?

   jinser <aimer@purejs.icu> — 2023-10-07T16:18:55Z

   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
   >
   
   
   
   

5. Re: Is `DATE` a function?

   Cory Albrecht <cory@albrecht.name> — 2023-10-16T15:42:49Z

   Rather than `SELECT DATE();` you likely want `SELECT NOW();`
   
   On Sat, Oct 7, 2023, 09:20 jinser <aimer@purejs.icu> wrote:
   
   > 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.
   >
   >
   >