can't create index with 'dowcast' row

Louis

what if you create one "wrapper" function immutable?
some thing like this.

CREATE OR REPLACE FUNCTION myextract(timestamp )
RETURNS date AS
$BODY$
BEGIN
return extract(date from $1) ;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE

best regards
mdc

--- Louis-David Mitterrand
<vindex+lists-pgsql-general@apartia.org> escribi�:

Hi,

To constraint unique'ness of my visitors to a 24h
periode I tried
created a index including the 'date' part of the
created_on timestamp:

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 "::"
LINE 1: ...buffer USING btree (id_session,
id_story, created_on::date);

and this:

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

How can I achieve what I am trying?

Thanks,

---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend

Yahoo! Encuentros.

Ahora encontrar pareja es mucho m�s f�cil, prob� el nuevo Yahoo! Encuentros http://yahoo.cupidovirtual.com/servlet/NewRegistration

Sorry forgot to mention

later try

CREATE UNIQUE INDEX visit_idx ON visit_buffer(
id_session, id_story ,myextract(created_on));

best regards

Louis

what if you create one "wrapper" function
immutable?
some thing like this.

CREATE OR REPLACE FUNCTION myextract(timestamp )
RETURNS date AS
$BODY$
BEGIN
return extract(date from $1) ;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE

best regards
mdc

--- Louis-David Mitterrand
<vindex+lists-pgsql-general@apartia.org> escribi�:

Hi,

To constraint unique'ness of my visitors to a 24h
periode I tried
created a index including the 'date' part of the
created_on timestamp:

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 "::"
LINE 1: ...buffer USING btree (id_session,
id_story, created_on::date);

and this:

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

How can I achieve what I am trying?

Thanks,

---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend

Yahoo! Encuentros.

Ahora encontrar pareja es mucho m�s f�cil, prob� el
nuevo Yahoo! Encuentros

http://yahoo.cupidovirtual.com/servlet/NewRegistration

Tarjeta de cr�dito Yahoo! de Banco Supervielle.
Solicit� tu nueva Tarjeta de cr�dito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar

Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.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

On Fri, Jan 25, 2008 at 12:17:16AM -0500, Tom Lane wrote:

Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.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.