ads' corner

If you have a table with an unique key field, want to insert new values but you don’t know if the value may already be in the table, you can use the following query:

1
2
3

CREATE TABLE uk_test (id SERIAL, col1 VARCHAR(20) NOT NULL UNIQUE, col2 VARCHAR(20) NOT NULL);
INSERT INTO uk_test (col1, col2) SELECT 'content 1', 'content 2' WHERE NOT EXISTS
(SELECT col1 FROM uk_test WHERE col1='content 1');

This does avoid unique key violation errors in a way, that no insert will happen if the value is already in the table.

I would like to thank depesz from #postgresql for the idea.