Skip to content

Avoid Unique Key violation

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.
  • Twitter
  • Bookmark Avoid Unique Key violation at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Avoid Unique Key violation
  • Bloglines Avoid Unique Key violation
  • Technorati Avoid Unique Key violation
  • Fark this: Avoid Unique Key violation
  • Bookmark Avoid Unique Key violation at YahooMyWeb
  • Bookmark Avoid Unique Key violation at Furl.net
  • Bookmark Avoid Unique Key violation at reddit.com
  • Bookmark Avoid Unique Key violation at blinklist.com
  • Bookmark Avoid Unique Key violation at Spurl.net
  • Bookmark Avoid Unique Key violation at Simpy.com
  • Bookmark Avoid Unique Key violation at blogmarks
  • Bookmark Avoid Unique Key violation with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

A. Heiduk on :

This does not work when two concurrent transactions try to insert the same data. In that case the second transaction will block until the first one is committed. After the commit the second transaction will wakeup again and return the unique constraint violation.
Comments ()

Ranjith Rajasekharan on :

This is best to small sized tables.But this is not a best method if the table size of uk_test is very large, since we are using select.
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options