1

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

Improve this question
0

5 Answers 5

Reset to default
5

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Use UNIQUE CONSTRAINT:

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

Comments

0

Instead of using multiple insert commands, you can use a select statement and the union will distinct the values.

insert into example (first, second, third, fourth, fifth)
select '1', '2', '3', '4', '5'
UNION
select '11', '22', '33', '44', '55'
UNION
select '111', '222', '333', '444', '555'
UNION 
select '1', '2', '3', '4', '5';

result:

 id | first | second | third | fourth | fifth 
----+-------+--------+-------+--------+-------
  5 | 111   | 222    | 333   | 444    | 555
  6 | 1     | 2      | 3     | 4      | 5
  7 | 11    | 22     | 33    | 44     | 55
(3 rows)
Improve this answer

1 Comment

Thanks, but this was just an example. Actually, in my application, I do not have all data together to do the INSERT together. Anyway thanks!
0 
CREATE UNIQUE INDEX idx_example
ON example(first, second, third, fourth, fifth);

it also accepts null values declare not null at table creation for fields required

Improve this answer

1 Comment

Please add further details to expand on your answer, such as working code or documentation citations.
0

You need to do that thing to avoid duplicate in postgresql: insert into table_name (column) SELECT your_value WHERE NOT EXISTS (SELECT 1 FROM table_name WHERE table_name.column='{}');

:) good luck!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.