Unexpected casts while using date_trunc()
От | Chris Bandy |
---|---|
Тема | Unexpected casts while using date_trunc() |
Дата | |
Msg-id | cb08676b-9a02-00da-2a1d-6ab262792004@gmail.com обсуждение исходный текст |
Ответы |
Re: Unexpected casts while using date_trunc()
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: