Avoid Unique Key violation

Andreas 'ads' Scherbaum

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 could use the following query:

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.