PostgreSQL – Unique constraint and null value

An important behavior in PostgreSQL to know about is the duplicate null values do not violate unique constraints.

Oracle

SQL> create table test (id number (2,0), 
                        country varchar(20) not null, 
                        state varchar(20)
                       );

Table created.

SQL> alter table test add constraint pk_test_id primary key (id);

Table altered.

SQL> alter table test add constraint uniq_test_cs unique (country, state);

Table altered.

PostgreSQL

admin@test # create table test (id numeric(2,0), 
                                country character varying(20) not null, 
                                state character varying(20)
                                );
CREATE TABLE
Time: 78.866 ms

admin@test # alter table test add constraint pk_test_id primary key (id);
ALTER TABLE
Time: 81.062 ms

admin@test # alter table test add constraint uniq_test_cs unique (country, state);
ALTER TABLE
Time: 82.800 ms

Let look at the way how null is handle with unique constraint in place.

Behaviour in Oracle

SQL> insert into test values (1, 'USA','SEATTLE');

1 row created.

SQL>  insert into test values (2, 'USA', 'OREGON');

1 row created.

SQL> insert into test (id, country) values (3, 'USA');

1 row created.

SQL> select * from test;

        ID COUNTRY              STATE
---------- -------------------- --------------------
         1 USA                  SEATTLE
         2 USA                  OREGON
         3 USA

SQL> insert into test (id, country) values (4, 'USA');
insert into test (id, country) values (4, 'USA')
*
ERROR at line 1:
ORA-00001: unique constraint (DEV.UNIQ_TEST_CS) violated


Behaviour in PostgreSQL

admin@test # insert into test values (1, 'USA','SEATTLE');
INSERT 0 1
Time: 79.928 ms

admin@test # insert into test values (2, 'USA', 'OREGON');
INSERT 0 1
Time: 72.490 ms

admin@test # insert into test (id, country) values (3, 'USA');
INSERT 0 1
Time: 75.906 ms
admin@test # select * from test;
+----+---------+---------+
| id | country |  state  |
+----+---------+---------+
|  1 | USA     | SEATTLE |
|  2 | USA     | OREGON  |
|  3 | USA     | NULL    |
+----+---------+---------+
(3 rows)

Time: 74.928 ms
admin@test # insert into test (id, country) values (4, 'USA');
INSERT 0 1
Time: 76.040 ms
admin@test # select * from test;
+----+---------+---------+
| id | country |  state  |
+----+---------+---------+
|  1 | USA     | SEATTLE |
|  2 | USA     | OREGON  |
|  3 | USA     | NULL    |
|  4 | USA     | NULL    |
+----+---------+---------+
(4 rows)

Time: 83.693 ms
admin@test #

2 ways to handle such situation —

1. Set default value for the column, which will case “duplicate key value violates” error.

2. Create Partial index.

admin@test # create unique index uniq_test_c on test(country) where state is null;
ERROR:  could not create unique index "uniq_test_c"
DETAIL:  Key (country)=(USA) is duplicated.
Time: 254.043 ms

admin@test # delete from test where id in (3,4);
DELETE 2
Time: 77.395 ms
admin@test # select * from test;
+----+---------+---------+
| id | country |  state  |
+----+---------+---------+
|  1 | USA     | SEATTLE |
|  2 | USA     | OREGON  |
+----+---------+---------+
(2 rows)

Time: 77.451 ms

admin@test # create unique index uniq_test_c on test(country) where state is null;
CREATE INDEX
Time: 78.431 ms

admin@test # insert into test (id, country) values (3, 'USA');
INSERT 0 1
Time: 75.413 ms

admin@test # insert into test (id, country) values (4, 'USA');
ERROR:  duplicate key value violates unique constraint "uniq_test_c"
DETAIL:  Key (country)=(USA) already exists.
Time: 95.083 ms
admin@test #

As per PostgreSQL documentation

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable

Advertisements

2 thoughts on “PostgreSQL – Unique constraint and null value

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s