(c1 WITH &&, (c2::circle) WITH &&)

WHERE (circle_center(c1) <> '(0,0)')

);

NOTICE: CREATE TABLE / EXCLUDE will create implicit index "circles_c1_c2_exclusion" for table "circles"

NOTICE: CREATE TABLE / EXCLUDE will create implicit index "circles_c1_c2_excl" for table "circles"

-- these should succeed because they don't match the index predicate

INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');

INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 4>');

-- succeed

INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>');

-- fail, overlaps

INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>');

ERROR: conflicting key value violates exclusion constraint "circles_c1_c2_exclusion"

ERROR: conflicting key value violates exclusion constraint "circles_c1_c2_excl"

DETAIL: Key (c1, (c2::circle))=(<(20,20),10>, <(0,0),4>) conflicts with existing key (c1, (c2::circle))=(<(10,10),10>, <(0,0),5>).

-- succeed because c1 doesn't overlap

INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>');

-- should fail on existing data without the WHERE clause

ALTER TABLE circles ADD EXCLUDE USING gist

(c1 WITH &&, (c2::circle) WITH &&);

NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "circles_c1_c2_exclusion1" for table "circles"

ERROR: could not create exclusion constraint "circles_c1_c2_exclusion1"

NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "circles_c1_c2_excl1" for table "circles"

ERROR: could not create exclusion constraint "circles_c1_c2_excl1"

DETAIL: Key (c1, (c2::circle))=(<(0,0),5>, <(0,0),5>) conflicts with key (c1, (c2::circle))=(<(0,0),5>, <(0,0),4>).

DROP TABLE circles;

-- Check deferred exclusion constraint

-- This should fail, but worth testing because of HOT updates

UPDATE deferred_excl SET f1 = 3;

ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);

NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "deferred_excl_f1_exclusion" for table "deferred_excl"

ERROR: could not create exclusion constraint "deferred_excl_f1_exclusion"

NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "deferred_excl_f1_excl" for table "deferred_excl"

ERROR: could not create exclusion constraint "deferred_excl_f1_excl"

DETAIL: Key (f1)=(3) conflicts with key (f1)=(3).

DROP TABLE deferred_excl;