PostgreSQL 9.2 - Conditional insert fails with duplicate key violation

Question

The following conditional insert works only if the item already exists! (does not go on with attempting to insert).

If the item does not exist, I get duplicate key violation (duplicate key violates unique constraint)! Using postgresql 9.2

INSERT INTO mytable (mytable_handle, title, description) 
select '1234/9876', 'Title here', 'description here' 
from mytable where not exists 
(select 1 from mytable where mytable_handle = '1234/9876')

(mytable_handle is the pkey)

Question: mytable_handle is a character columns? As a primary key? — joop
Actually we are using strings for that field in the likes of '1234/9876'. Just updated the question on this. — pkaramol

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2016-01-22T12:12:31

This query:

select '1234', 'Title here', 'description here' 
from mytable where not exists 
(select 1 from mytable where mytable_handle = '1234');

will return the tuple '1234', 'Title here', 'description here' once for each row that already exists in the table, if the mytable_handle value does not exist. Run the select on its own and you will see for yourself

You want:

select '1234', 'Title here', 'description here' 
where not exists 
(select 1 from mytable where mytable_handle = '1234');

SQLFiddle example: http://sqlfiddle.com/#!15/b331f/1

PostgreSQL 9.2 - Conditional insert fails with duplicate key violation

1 Answers