Avoid duplicates in postgreSQL

Solution 1:

This

ALTER TABLE example ADD CONSTRAINT constraintname UNIQUE (first, 
second, third, fourth, fifth);

will not work, because of nullable column:

third VARCHAR (255)

You can check it like that:

INSERT INTO example(first, second, third, fourth, fifth) values('1', '2', NULL, '4', '5');
INSERT INTO example(first, second, third, fourth, fifth) values('1', '2', NULL, '4', '5)';

SELECT * FROM example;

output:

|id|first|second|third|fourth|fifth|
|1|1|2|NULL|4|5|
|2|1|2|NULL|4|5|

You should either:

1. set not null: ALTER TABLE example ALTER COLUMN third SET NOT NULL;

2. two indexes with NULL and NOT NULL conditions:

   CREATE UNIQUE INDEX ON example(first, second, fourth, fifth) WHERE third IS NULL;
   CREATE UNIQUE INDEX ON example(first, second, third, fourth, fifth) WHERE third IS NOT NULL;
   

3. if it COALSCE(third,'') ok for your case, then:

   CREATE UNIQUE INDEX ON example (first, second, COALESCE(third, ''), fourth, fifth);
   

Solution 2:

Use UNIQUE CONSTRAINT:

ALTER TABLE example ADD CONSTRAINT constraintname UNIQUE (first, second, third, fourth, fifth);