From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Error in exclusion constraint error message (8.5)? |
Date: | 2010-01-02 13:37:32 |
Message-ID: | 20100102133732.GA7835@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
I tried to use exclusion for time ranges, with this table and data:
CREATE TABLE test (
from_ts TIMESTAMPTZ,
to_ts TIMESTAMPTZ,
CHECK ( from_ts < to_ts ),
CONSTRAINT overlapping_times EXCLUDE USING GIST (
box(
point( extract(epoch FROM from_ts at time zone 'UTC'), extract(epoch FROM from_ts at time zone 'UTC') ),
point( extract(epoch FROM to_ts at time zone 'UTC') , extract(epoch FROM to_ts at time zone 'UTC') )
) WITH &&
)
);
INSERT INTO test ( from_ts, to_ts ) VALUES ( '2009-01-01 01:23:45 EST', '2009-01-10 23:45:12 EST' );
INSERT INTO test ( from_ts, to_ts ) VALUES ( '2009-02-01 01:23:45 EST', '2009-02-10 23:45:12 EST' );
INSERT INTO test ( from_ts, to_ts ) VALUES ( '2009-01-08 00:00:00 EST', '2009-01-15 23:59:59 EST' );
3rd insert fails (correctly), but I have doubts about its error message, which was:
psql:z.sql:18: ERROR: conflicting key value violates exclusion constraint "overlapping_times"
DETAIL: Key (box(point(date_part('epoch'::text, timezone('UTC'::text, from_ts)), date_part('epoch'::text, timezone('UTC'::text, from_ts))), point(date_part('epoch'::text, timezone('UTC'::text, to_ts)), date_part('epoch'::text, timezone('UTC'::text, to_ts)))))=((1232078399,1232078399),(1231387200,1231387200)) conflicts with existing key (box(point(date_part('epoch'::text, timezone('UTC'::text, from_ts)), date_part('epoch'::text, timezone('UTC'::text, from_ts))), point(date_part('epoch'::text, timezone('UTC'::text, to_ts)), date_part('epoch'::text, timezone('UTC'::text, to_ts)))))=((1232078399,1232078399),(1231387200,1231387200)).
Please not that both box values are the same, and relate to row:
( '2009-01-08 00:00:00 EST', '2009-01-15 23:59:59 EST' )
while I would assume that one of the boxes in error message should relate to row:
( '2009-01-01 01:23:45 EST', '2009-01-10 23:45:12 EST' )
I.e. contain box: (1231645512,1231645512),(1230787425,1230787425)
Is it my lack of understanding? If yes, what is the rationale behind providing the same value twice?
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-01-02 17:55:01 | Re: Error in exclusion constraint error message (8.5)? |
Previous Message | Mark Williamson | 2009-12-30 20:23:14 | remove |