I have an application generating the following query on a DATE column in
PostgreSQL 10.1:
> SELECT TO_CHAR(DATE_TRUNC('month', jobs.active_until), 'YYYY-MM')
> FROM jobs
> GROUP BY DATE_TRUNC('month', jobs.active_until)
> LIMIT 500
I wanted to support it with an expression index, but was surprised to
find that the DATE_TRUNC call was not immutable:
> CREATE INDEX ON jobs (DATE_TRUNC('month', active_until));
> ERROR: functions in index expression must be marked IMMUTABLE
The documentation explains that DATE is first cast to TIMESTAMP. (As I
understand it, this is an immutable cast; sounds find and appropriate.)
https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
But in my testing, the date value is actually cast to TIMESTAMPTZ:
> SELECT pg_typeof(DATE_TRUNC('month', active_until))
> FROM jobs LIMIT 1;
> pg_typeof
> --------------------------
> timestamp with time zone
> (1 row)
Indeed, casting to TIMESTAMP first allows me to create the index:
> CREATE INDEX ON jobs (DATE_TRUNC('month', active_until::timestamp));
However, this index doesn't help me because the query is generated by an
application outside of my control.
1. It seems to me it is worth mentioning in the docs that DATE_TRUNC
accepts and returns TIMESTAMPTZ. N.B. There's no mention of it in the
table of functions:
https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TABLE
2. Since this implicit cast to TIMESTAMPTZ is used rather than
TIMESTAMP, it may be worthwhile to add an explicit implementation that
accepts DATE.
(Thanks to Andrew Gierth for explaining the mechanics on IRC.)
-- Chris