Re: can't create index with 'dowcast' row

Louis-David Mitterrand <vindex+lists-pgsql-general(at)apartia(dot)org> writes:
> CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session,
> id_story, created_on::date);

> psql:visit_pkey.sql:5: ERROR: syntax error at or near "::"

The reason that didn't work is that you need parentheses around an index
expression (otherwise the CREATE INDEX syntax would be ambiguous).

> CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, extract(date from created_on));
> psql:visit_pkey.sql:4: ERROR: functions in index expression must be marked IMMUTABLE

I take it created_on is timestamp with time zone, not plain timestamp?
The problem here is that the coercion to date is not immutable because
it depends on the timezone setting. (The other way would have failed
too, once you got past the syntax detail.) You need to figure out
what your intended semantics are --- in particular, whose idea of
midnight should divide one day from the next --- and then use a
unique index on something like

((created_on AT TIME ZONE 'Europe/Paris')::date)

Note that the nearby recommendation to override the immutability
test with a phonily-immutable wrapper function would be a real bad
idea, because such an index would misbehave anytime someone changed
their timezone setting.

regards, tom lane