Upserting via Writeable CTE

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.

pagila=# select \* from actor where first_name = 'AMBER' and last_name = 'LEE';
 actor_id | first_name | last_name | last_update 
----------+------------+-----------+-------------
(0 rows)

pagila=# with upsert as 
pagila-# (update actor set last_update = now() where first_name = 'AMBER' and last_name = 'LEE' returning actor_id) 
pagila-# insert into actor (first_name, last_name, last_update) 
pagila-# select 'AMBER', 'LEE', now() WHERE NOT EXISTS (SELECT 1 FROM upsert) ; 
INSERT 0 1

pagila=# select \* from actor where first_name = 'AMBER' and last_name = 'LEE'; 
 actor_id | first_name | last_name |        last_update         
----------+------------+-----------+----------------------------
     201 | AMBER      | LEE       | 2011-03-16 11:29:15.611445
(1 row)

pagila=# with upsert as 
pagila-#(update actor set last_update = now() where first_name = 'AMBER' and last_name = 'LEE' returning actor_id) 
pagila-# insert into actor (first_name, last_name, last_update) 
pagila-# select 'AMBER', 'LEE', now() WHERE NOT EXISTS (SELECT 1 FROM upsert) ; 
INSERT 0 0

pagila=# select \* from actor where first_name = 'AMBER' and last_name = 'LEE'; 
 actor_id | first_name | last_name |        last_update         
----------+------------+-----------+----------------------------
     201 | AMBER      | LEE       | 2011-03-16 11:30:21.244226
(1 row)

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.