1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. 4 NULL Handling
  5. 4.3 NULL in Unique Constraints

4.3 NULL in Unique Constraints

A unique column can be defined in a table by a unique constraint or unique index. Oracle standard allows multiple nulls in a unique column, and that is how PostgreSQL behaves. Both single column and composite unique index behave similar in Oracle and PostgreSQL database.

OraclePostgreSQL
CREATE TABLE null_test(
  tid   NUMBER,
  tname VARCHAR2(10),
  tdate DATE
);

ALTER TABLE null_test ADD CONSTRAINT null_test_unq UNIQUE(tid);

INSERT INTO null_test VALUES(1, 'test-1', current_date);
INSERT INTO null_test VALUES(NULL, 'test-n1', current_date);
INSERT INTO null_test VALUES(NULL, 'test-n2', current_date);
INSERT INTO null_test VALUES(NULL, 'test-n3', current_date);
commit;
CREATE TABLE null_test(
  tid   INTEGER,
  tname VARCHAR(10),
  tdate DATE
);

ALTER TABLE null_test ADD CONSTRAINT null_test_unq UNIQUE(tid);

INSERT INTO null_test VALUES(1, 'test-1', current_date);
INSERT INTO null_test VALUES(NULL, 'test-n1', current_date);
INSERT INTO null_test VALUES(NULL, 'test-n2', current_date);
INSERT INTO null_test VALUES(NULL, 'test-n3', current_date);
Was this article helpful to you? Yes No

How can we help?