dinsdag 14 januari 2014

Why I like PostgreSQL more than MySQL: RETURNING

Every once in a while you get a request like "When I run this delete query I want to see which records have been deleted.".

The quick and dirty solution would be to run a SELECT  FOR UPDATE query first, to get the data, and follow that with a DELETE query to remove the data. But that requires two roundtrips to the database and if the query parameters are complex then you basically make the whilething twice as slow as it needs to be.

In PostgreSQL you just add "RETURNING *" to the DELETE query.

CREATE TABLE demo (id INTEGER, firstname TEXT, score INTEGER);
INSERT INTO demo VALUES (1,'Carl',24), (2,'Pete',10);

DELETE FROM demo WHERE firstname='Pete' RETURNING *;

That removes the record for 'Pete', and returns it, in one trip to the database.

Similarly, you can fetch the new record after an update:

UPDATE demo SET score=score+6 WHERE firstname='Carl' RETURNING score;

which returns just one record with score='30', the new score for Carl after adding 6 to it.