Upserting via Writeable CTE

Mar 16th, 2011

Earlier today my colleague Depesz posted a nice write up showing one of the use cases for the new 9.1 feature, “Writable CTE’s”. It certainly shows one handy thing that this feature will enable, but it’s not the only one. Here’s a quick little bit of SQL I have been playing with for some time that re-implements the infamous “UPSERT” command (a long time sticking point for people trying to make MySQL apps more cross-database compatible) into Postgres.

Now, to be fair, this bit of SQL does have a race condition (think two people trying to insert the same actor at the same time), so it doesn’t really solve all of your problems, but if you are looking for a quick hack, it might just do the trick. Also don’t be afraid to play with it; this was like 2 minutes of thought and making sure the syntax worked; you could certainly try turning it around or coming up with other variants. That’s actually one of the coolest things about this feature; waiting to see what use cases people come up with for it.

A veteren free software developer and advocate, I tend to speak and write about open source and large scale web operations, although most people know me from my work as a major contributor to the Postgres project.