Re: Range constraint with unique index - Mailing list pgsql-general

From Kevin Grittner
Subject Re: Range constraint with unique index
Date
Msg-id 20121108220817.77870@gmx.com
Whole thread Raw
In response to Range constraint with unique index  ("Steven Plummer" <splummer@mttltd.com>)
List pgsql-general
Steven Plummer wrote:

> I have a table that has an integer and a int8range.

> What I want is to add a constraint that stops anyone adding

Maybe you want an exclusion constraint:

http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

For example:

CREATE EXTENSION btree_gist ;
CREATE TABLE t1 (c1 INT NOT NULL,
                 c2 int8range NOT NULL,
                 EXCLUDE USING gist (c1 WITH =, c2 WITH &&));
INSERT INTO t1 VALUES (1, '[1,100]');

Now try to insert an overlapping row:

INSERT INTO t1 VALUES (1, '[51,150]');

-Kevin


pgsql-general by date:

Previous
From: "Steven Plummer"
Date:
Subject: Range constraint with unique index
Next
From: Craig Ringer
Date:
Subject: Re: Does PostgreSQL have complete functional test cases?