Exclusion constraints not taking effect in postgresql?

Question

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

The constraint is not violated. Could you explain what you're expecting it to do? — Nick Barnes
– Nick Barnes, Commented Aug 13, 2015 at 13:57
Well, the name "Paul" appeared twice, which is a violation, right? — qed
– qed, Commented Aug 13, 2015 at 13:59
No, like @Sathish said, the exclusion constraint means that you cannot have two rows where the names are equal and the ages are not equal. If both are equal, it's allowed. — Nick Barnes
– Nick Barnes, Commented Aug 13, 2015 at 14:01

Sathish · Accepted Answer · 2015-08-13 13:04:36Z

 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).

Collectives™ on Stack Overflow

Exclusion constraints not taking effect in postgresql?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related