Upserting via Writeable CTE

Wednesday, March 16. 2011

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.

Posted by Robert Treat in mysql, postgres, sql at 11:40 | Comments (4) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

With a new implementation of SERIALIZABLE level, there should not be a race condition.

#1 Pavel Stehule (Homepage) on 2011-03-16 15:12 (Reply)

Our SERIALIZABLE implementation just throws an error here; a UNIQUE index would do the same trick.

#1.1 Marko Tiikkaja on 2011-03-16 15:16 (Reply)

CREATE RULE insert_ignore AS
ON INSERT TO actor WHERE (EXISTS (SELECT true FROM actor as ul WHERE ( ul.first_name,ul.last_name) = (NEW.first_name, NEW.last_name))) DO INSTEAD NOTHING;

and you can just use plain INSERT !

#2 deem0n on 2011-03-18 05:41 (Reply)

You really, REALLY should not use rules for anything like this. While in this case the rule might accidentally work (sans the race condition), it will fail horribly if you attempt to implement the UPSERT idea in the original post.

#2.1 Marko Tiikkaja on 2011-03-18 11:32 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images.
	Remember Information? Subscribe to this entry

Quicksearch

Hi! I'm Robert Treat, COO of OmniTI, perhaps the best internet technology consulting company on the planet.

A veteran open source developer and advocate, I have been recognized as a major contributor to the PostgreSQL project, and can often be found speaking on open source, databases, and large scale web operations.

Upcoming Events

PGDay DC 2012

March 30th

At Reston, Virginia

PGCon 2012

May 15th - 18th

At Ottawa, Canada

Velocity 2012

June 25th - 27th

At Santa Clara, California

Surge 2012

September

At Baltimore, Maryland

Recent Musings

I built a node site

Tuesday

Intrest free (technical) debt is risky

Monday

Monitoring for "E-Tailers"

Wednesday

You were saying?

Javier Salado about Intrest free (technical) debt is risky
Tue, 07.02.2012 05:16
Hi Robert, Tanks for your t houghtful interest in my lates t post. You are absolutely right about the underlyi [...]

about Checkpoints, Buffers, and Graphs
Tue, 20.12.2011 10:49
thanks for the slides and the post.

Greg Smith about Checkpoints, Buffers, and Graphs
Sun, 27.11.2011 15:42
And the slides are up at http: //www.2ndquadrant.com/en/talks /