CODEWITHSUNDEEP
postgresql
qed
qed

Reputation: 23104

Exclusion constraints not taking effect in postgresql?

Here is the code:

testdb=# CREATE TABLE COMPANY7(
testdb(#    ID INT PRIMARY KEY     NOT NULL,
testdb(#    NAME           TEXT ,
testdb(#    AGE            INT   ,
testdb(#    ADDRESS        CHAR(50),
testdb(#    SALARY         REAL,
testdb(#    EXCLUDE USING gist
testdb(#    (NAME WITH =,
testdb(#    AGE WITH <>)
testdb(# );
CREATE TABLE
testdb=#  INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT 0 1
testdb=#  INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT 0 1
testdb=#  INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
INSERT 0 1
testdb=# table company7
testdb-# ;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Paul  |  32 | Texas                                              |  20000
  3 | Allen |  42 | California                                         |  20000
(3 rows)

Clearly both constraints are violated, but there is no error, why?

PostgreSQL version 9.4.4

Tested on Ubuntu 15.04 and OS X 10.10

Upvotes: 0

Views: 74

Answers (1)

Sathish
Sathish

Reputation: 4487

   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)

Which means same Name and Different Age not Allowed.

I tried like this it show error

   INSERT INTO appsetup.COMPANY7 VALUES(4, 'Paul', 42, 'California', 20000.00 );

ERROR

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

Upvotes: 3

Related Questions