From: | Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)de> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | unique index for periods |
Date: | 2009-08-20 06:58:19 |
Message-ID: | 20090820065819.GA2598@gheift.kawo1.rwth-aachen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I try to create an unique index for a (time)period, and my goal is to
prevent two overlapping periods in a row.
For this I created a type with following command:
CREATE TYPE period AS
("first" timestamp with time zone,
"next" timestamp with time zone);
To use the btree index I added a compare function:
CREATE OR REPLACE FUNCTION period_compare(period, period)
RETURNS integer AS $BODY$
begin
raise info 'compare % <=> % = %', $1, $2,
CASE
WHEN $1.next <= $2.first THEN -1
WHEN $2.next <= $1.first THEN 1
ELSE 0
END;
return
CASE
WHEN $1.next <= $2.first THEN -1
WHEN $2.next <= $1.first THEN 1
ELSE 0
END;
end
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 1;
After this I created a operator class:
CREATE OPERATOR CLASS period_overlap
DEFAULT FOR TYPE period USING btree AS
FUNCTION 1 period_compare(period, period);
To test everything I use this table:
CREATE TABLE p (
p period NOT NULL,
CONSTRAINT p_pkey PRIMARY KEY (p)
);
Now I fill the table with data:
DELETE FROM p;
-- clean up
VACUUM p;
INSERT INTO p VALUES (('-infinity', 'today')::period);
-- this one fails
-- INSERT INTO p VALUES (('-infinity', 'infinity')::period);
DELETE FROM p;
-- the index tree is still there, why?
INSERT INTO p VALUES (('-infinity', 'infinity')::period);
-- intersects with the deleted value, so compare returns 0
-- and the data goes to the left side of the tree
-- this one should fail
INSERT INTO p VALUES (('today', 'infinity')::period);
-- but this one is bigger than the deleted value, goes to
-- the right side of the tree and is not compared to the
-- entry inserted above.
What do I do wrong? Is there another solution to solve my problem?
Thanks,
Gerhard
From | Date | Subject | |
---|---|---|---|
Next Message | Sim Zacks | 2009-08-20 07:36:37 | Re: multiple paramters in aggregate function |
Previous Message | Craig Ringer | 2009-08-20 02:33:10 | Re: Problem with bacula and 8.3/8.4 |