I am currently rewritting my ordr interface from scratch, as it's currently using SQLite as backend which pretty much sucks. So during the rewrite I had the problem to avoid unique key violations when inserting the photos/photosets from the Flickr API to my database. The following solution helps to get this done:
QUOTE:
INSERT INTO ordr.photosets
SELECT 12345678,
87654321,
10,
'Testtitel',
'Just a long description Text',
'123MyUID@Flickr'
WHERE NOT EXISTS (
SELECT sid FROM ordr.photosets WHERE sid = 12345678
);
Side note: This works with PostgreSQL. I'm not sure if this will also work with other RDBMS. IIRC Sybase for instance has it's own function for such tasks. S. th. like this:
QUOTE:
IF NOT EXISTS
(SELECT * FROM MyTable WHERE name = 'FooBar')
INSERT INTO MyTable VALUES('FooBar', 50, 'Even more FooBar')
Update: AndrewSN from #postgresql on irc.freenode.net mentioned that my query has a race condition.
QUOTE:
[22:31:32] <AndrewSN> Doomshammer: if there's a concurrent insert in another transaction that isn't committed yet, then the "not exists" won't be able to see it
[22:32:09] <AndrewSN> Doomshammer: so your statement can still error out with duplicate key errors
Which of course is absolutelly true. So my example is not a good solution. But luckily PostgreSQL's exellent documentation has a solution for nearly every problem. So this is what AndrewSN pointed out:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLEThanks a lot AndrewSN!
).
