1

I have many rows of data to be inserted into a table. Table already has data in it. When I do a bulk insert like this,

INSERT INTO permission(username, permission) values(('john','ticket_view'), ('john','ticket_modify'), ('john','ticket_approve'));

But my table already has a row john, ticket_view In that case my above insert query ends in an error duplicate key value violates unique constraint.

How can I ignore this duplicate issue in postgresql 9.3? I just want to insert all the rows. If a similar row exists I want to ignore it. How can I do that? I welcome your help.

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Velu narasimman
  • 333
  • 3
  • 16
  • Postgres 9.3 is longer [supported](https://www.postgresql.org/support/versioning/) If you upgrade to an up-to-date version (e.g. 11) you can easily use the `on conflict` feature introduced in 9.5 – a_horse_with_no_name Dec 11 '18 at 10:52
  • @a_horse_with_no_name, if I upgrade my postgres to latest version how should I write the `ON CONFLICT` syntax for bulk inserts as I said in my question? can you give me a sample syntax? – Velu narasimman Dec 11 '18 at 11:01
  • Please read the duplicate questions. All of them answer your question (including solutions for your outdated version) – a_horse_with_no_name Dec 11 '18 at 11:03
  • The questions mentioned as duplicates are not really duplicates of this question. OP wants to insert rows and ignore, not update, and is not interested specifically in any Postgres syntax for the insert ignore / update he just wants to get the rows in. This is really a more simliar to question to this one: https://stackoverflow.com/questions/47541579/how-to-have-postgres-ignore-inserts-with-a-duplicate-key-but-keep-going/ - see my answer there for a simple way to do this in Postgres 9.3 – nzn Apr 07 '20 at 08:50

1 Answers1

6

Assuming you are using Postgres 9.5 or higher, you may try using ON CONFLICT and also rephrase your insert as an INSERT INTO ... SELECT:

INSERT INTO permission (username, permission)
SELECT 'John', 'ticket_view' UNION ALL
SELECT 'John', 'ticket_modify' UNION ALL
SELECT 'John', 'ticket_approve'
ON CONFLICT (username, permission) DO NOTHING;

If you can't use the above solution because you have too much data, then one option would be to first insert your tuples into a temporary table, and then use the above query, e.g.

CREATE TEMPORARY TABLE temp_permission (
    username VARCHAR NOT NULL,
    permission VARCHAR NOT NULL
)

INSERT INTO temp_permission (username, permission)
VALUES
    (('John', 'ticket_view'),
     ('John', 'ticket_modify'),
     ('John', 'ticket_approve'));

Then, use the following query to insert while ignoring duplicates:

INSERT INTO permission (username, permission)
SELECT username, permission
FROM temp_permission
ON CONFLICT (username, permission) DO NOTHING;

Afterwards, you may drop the temp table.

Tim Biegeleisen
  • 387,723
  • 20
  • 200
  • 263
  • 1
    The question explicitly says PG 9.3 – 404 Dec 11 '18 at 10:54
  • @404 There isn't a particularly nice way to do this in < 9.5. Maybe my answer would still be helpful. – Tim Biegeleisen Dec 11 '18 at 10:56
  • @TimBiegeleisen, I like your answer but the values I am passing in are some dynamic data so I can explicitly write `select` statements like you said. How can I achieve the same for such dynamic data? – Velu narasimman Dec 11 '18 at 11:04
  • Insert your `VALUES` first into a temporary table, and then use my answer. – Tim Biegeleisen Dec 11 '18 at 11:16
  • @TimBiegeleisen, I have upgraded postgres to version 11. and I used INSERT INTO permission(username, permission) values(('john','ticket_view'), ('john','ticket_modify'), ('john','ticket_approve')) ON CONFLICT DO NOTHING; now I am getting this error ` syntax error at or near "on"`. I am struggling a lot but no clues why this is stopping the query! please help me! – Velu narasimman Dec 11 '18 at 14:25
  • The query you wrote won't work, I think. Have a look at my updated answer. – Tim Biegeleisen Dec 11 '18 at 14:32