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

On Fri, Jan 25, 2008 at 12:17:16AM -0500, Tom Lane wrote:
> 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).

This worked fine once I changed the type to a simple 'timestamp'.

> > 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.

Thanks Tom for that explanation.