Re: the date() function is undocumented while quite necessary - Mailing list pgsql-docs

PG Doc comments form <noreply@postgresql.org> writes:
> I can't find anywhere the documentation of the SQL DATE() function which
> extracts the date part out of a timestamp.

It's a type cast written in function-like syntax, as documented in

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

We don't really encourage that notation, because it's confusing
and it doesn't work for every possible type name, which is why
you won't find it mentioned very many places.

> Please note that according to my tests, one can't use the cast operator when
> defining expression-based indexes, while the date(column) expression is
> accepted.

You can if you put parentheses around it:

regression=# create table t1 (f1 timestamp);
CREATE TABLE
regression=# create index on t1 (f1::date);
ERROR:  syntax error at or near "::"
LINE 1: create index on t1 (f1::date);
                              ^
regression=# create index on t1 ((f1::date));
CREATE INDEX

This is per the restriction explained in the CREATE INDEX docs, that
any expression-to-be-indexed that doesn't look like a function call
requires parentheses.

https://www.postgresql.org/docs/current/indexes-expressional.html

https://www.postgresql.org/docs/current/sql-createindex.html

            regards, tom lane