Re: [GENERAL] can't create index with 'dowcast' row
On Fri, Jan 25, 2008 at 12:17:16AM -0500, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] can't create index with 'dowcast' row
Louis-David Mitterrand <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] can't create index with 'dowcast' row
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 > <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 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 <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] can't create index with 'dowcast' row
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
