I have a table with a timestamptz column for the "effective date/time"
of the row, and need to have some queries that look only for those
entries for which that is in the future or VERY recently - which will
be a small minority of rows. I'm looking at something like:
CREATE INDEX on tablename (effective) where effective>timestamptz
'now'-interval '21 days'
with a possible REINDEX or ALTER INDEX or even DROP INDEX/CREATE INDEX
periodically to "prune" the index. However, Postgres complains:
ERROR: functions in index predicate must be marked IMMUTABLE
Without the arithmetic, it works fine:
CREATE INDEX ledgernotyet on larcombe.ledger (lid,effective) where
effective>timestamptz 'now'
Is there a way around this? Also, how would I go about pruning the
index, preferably in such a way that the old index can be used?
Thanks!
Chris Angelico
