3 ms·
> Yeah, TIMESTAMPTZ expressions don’t index nicely because they’re nondeterministic Could you expand on why it's not deterministic? I thought timestamptz was 8
by sa46 3y ago
> Yeah, TIMESTAMPTZ expressions don’t index nicely because they’re nondeterministic
Could you expand on why it's not deterministic? I thought timestamptz was 8-byte microseconds since the epoch. Is the problem that the timestamptz uses the server timezone?
- wswope 3y agoYeah, you've got the right idea. Specifically, functions that operate on TIMESTAMPTZ aren't typically marked as IMMUTABLE (because their output implicitly depends on the connection-level time zone setting), and that IMMUTABLE flag is required for functions used in indexed expressions. https://www.postgresql.org/docs/current/xfunc-volatility.html https://www.postgresql.org/docs/current/xfunc-volatility.htm...