Avoid duplicates in postgreSQL

I’m using postgreSQL version 10.3 and I have a table like this:

CREATE TABLE IF NOT EXISTS example (
  id SERIAL PRIMARY KEY, 
  first VARCHAR(64) NOT NULL, 
  second VARCHAR(255) NOT NULL, 
  third VARCHAR (255), 
  fourth VARCHAR (4) NOT NULL, 
  fifth VARCHAR(12) NOT NULL);

I want to make numerous INSERT and make sure there are no duplicates. So I thought about using INSERT INTO ... ON CONFLICT DO NOTHING;.

The idea is to insert the record only if the values on the columns first | second | third | fourth | fifth are not already present in the table. The problem is that the id is incremented automatically, so each record is different and therefore the INSERT always takes place.

Here we see what I mean:

db=# CREATE TABLE IF NOT EXISTS example (id SERIAL PRIMARY KEY, first VARCHAR(64) NOT NULL, second VARCHAR(255) NOT NULL, third VARCHAR (255), fourth VARCHAR (4) NOT NULL, fifth VARCHAR(12) NOT NULL);
CREATE TABLE
db=# dt
           Lista delle relazioni
 Schema |  Nome   |  Tipo   | Proprietario
--------+---------+---------+--------------
 public | example | tabella | admin
(1 riga)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('1', '2', '3', '4', '5') ON CONFLICT DO NOTHING;
INSERT 0 1

db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
(1 riga)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('11', '22', '33', '44', '55') ON CONFLICT DO NOTHING;
INSERT 0 1
db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
  2 | 11    | 22     | 33    | 44     | 55
(2 righe)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('111', '222', '333', '444', '555') ON CONFLICT DO NOTHING;
INSERT 0 1
db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
  2 | 11    | 22     | 33    | 44     | 55
  3 | 111   | 222    | 333   | 444    | 555
(3 righe)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('1', '2', '3', '4', '5') ON CONFLICT DO NOTHING;
INSERT 0 1
db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
  2 | 11    | 22     | 33    | 44     | 55
  3 | 111   | 222    | 333   | 444    | 555
  4 | 1     | 2      | 3     | 4      | 5
(4 righe)

The record (2 | 11 | 22 | 33 | 44 | 55) is equal to the record (4 | 1 | 2 | 3 | 4 | 5). They differ only for the id. I would not like the record (4 | 1 | 2 | 3 | 4 | 5) to be inserted. How can I do?

Thank you

Answer

Use UNIQUE CONSTRAINT:

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

Advertisement

Answer