PeterVandivier
Sadly, in the process of creating a calendar table, I have encountered the following problem defining a calculated column.
<>https://dbfiddle.uk?rdbms=postgres_13&fiddle=98dfb76e3a27be8f4fa94185a2e7b00d&hide=2
Fair enough, [`extract()` is not documented `immutable`][3], so [I can't use it in a calculated column][1]. Instead I can use the following construction with the single-input `to_timestamp()`^(though^ ^also^ ^not^ [^documented^][4] ^immutable^ ^🤔)^ as a workaround.
<>https://dbfiddle.uk?rdbms=postgres_13&fiddle=98dfb76e3a27be8f4fa94185a2e7b00d&hide=1
It seems like I could also [shoehorn in a plpgsql language wrapper and just _promise_ the engine it's immutable][2] to get the first approach to work. If however I were the kind of person obsessive enough to let it bother me, is there a pure-SQL inline way I could alter the first construction to allow for the `timestamp`-to-epoch conversion in the calculated field?
[1]: https://www.postgresql.org/docs/12/ddl-generated-columns.html
[2]: https://fluca1978.github.io/2019/11/04/PostgreSQL12GeneratedColumns.html
[3]: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
[4]: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TABLE
Top Answer
Jack Douglas
Yes, you can: just convert to `timestamp` at UTC. This is not what you might expect.
The details:
* `extract` is re-written to a call to `date_part` internally, as you can see from the execution plan:
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=c123982b1406e6ab45f2906b0c1c3fd0
* You might think that `extract(epoch from my_timestamp_with_tz))` should be `immutable`, but it isn't, it is `stable`:
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=790805711a2409514b9b349f808f0d92
* The reason why it isn't, and indeed shouldn't be, is that a function can't be declared immutable for some arguments and not others. The other 'parts' that can be extracted clearly aren't immutable as the result depends on the time
zone setting for the session:
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=801b3f997641b5b9f8c2cbb1b0988818
* If you could have the functions immutable for some arguments and not others, you would want it arranged like this:
type|epoch|other part
----|-----|----------
timestamp|stable|immutable
timestamptz|immutable|stable
* Instead, and ironically, `extract(epoch from my_timestamp_without_tz))` *is* considered immutable (even for 'epoch') — the input is then assumed to be UTC. The current behaviour has existed since v9.2, when the pre-7.3 behaviour was restored — read [this mailing list thread](https://www.postgresql.org/message-id/flat/20120125161311.GA7600%40depesz.com#4c132cb6448a9cd7ae1fe2f3a3152541) for more details.
* It would make much more sense to me for postgres to provide an entirely separate `to_epoch` function and remove 'epoch' from `extract`/`date_part`. Then you could have:
function|type|volatility
--------|----|----------
date_part|timestamp|immutable
date_part|timestamptz|stable
to_epoch|timestamptz|immutable
`to_epoch(timestamp)` doesn't make sense so we can ignore that case.
---
So, you *can* get the behaviour you want, very counter-intuitively, by [converting your `timestamptz` to UTC](https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT):
<>https://dbfiddle.uk/?rdbms=postgres_13&fiddle=bcdb1ae14ff71ceae7fd5c27bb883224&hide=2
> seems like I could also shoehorn in a plpgsql language wrapper and just promise the engine it's immutable to get the first approach to work.
You could also do that instead if you wanted to, as people [evidently had to](https://www.depesz.com/2012/02/13/waiting-for-9-2-named-parameters-in-sql-functions/) pre v9.2.