I can create a functional index on date(timestamp) but not date(timestamptz).
Version 7.3.2
=> CREATE TABLE foo (ts_with timestamp with time zone,
ts_without timestamp without time zone);
CREATE TABLE
=> CREATE INDEX foo1_idx ON foo ( date(ts_with) );
ERROR: DefineIndex: index function must be marked IMMUTABLE
=> CREATE INDEX foo2_idx ON foo ( date(ts_without) );
CREATE INDEX
I can't think why one would be immutable and the other not, so I updated
pg_proc setting provolatile='i' for the function in question. All seems OK.
I haven't got the source to hand - can anyone verify this isn't an RPM glitch
(seems unlikely)?
Can any developers confirm that switching the flag is safe (can't think why
not)?
--
Richard Huxton