Thread: Range constraint with unique index

This email transmission is confidential and intended solely for the person or organisation to whom it is addressed. If you are not the intended recipient, you must not copy, distribute or disseminate the information, or take any action in reliance of it. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of any organisation or employer. If you have received this message in error, do not open any attachment but please notify the sender (above) before deleting this message from your system. Please rely on your own virus check as no responsibility is taken by the sender for any damage rising out of any bug or virus infection. Registered Office: Motor Trade Technologies Limited, 33 The Clarendon Centre, Salisbury Business Park, Dairy Meadow Lane, Salisbury, Wiltshire, SP1 2TJ. Company No. 05573816

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