Re: PL/pgSQL trigger and sequence increment

jonesd(at)xmission(dot)com writes:
> CREATE UNIQUE INDEX one_entry_per_submitter_per_month ON table_entry
> (submitter_id , date_trunc('month',entry_timestamp));
> runs into
> ERROR: functions in index expression must be marked IMMUTABLE.

> If I'm reading this correctly, date_trunc is not IMMUTABLE and thus
> not usable in an index.

It is not immutable because it depends on the timezone setting: the same
timestamptz might be truncated to different absolute time instants
depending on which zone you are in. IOW, when is midnight of the first
of the month, exactly?

You could work around this with something like

date_trunc('month',entry_timestamp AT TIME ZONE 'UTC')

(feel free to substitute a different zone name reflecting what you want
to have happpen) but I wonder whether this doesn't reflect a gap in your
database specification.

regards, tom lane