On 24 Sep 2010, at 21:20, Bartłomiej Korupczyński wrote: > Hi guys, > > I've found some posts from 2007 about UPDATE/DELETE ... LIMIT N syntax > and I'd like to raise it again. Current PostgreSQL of UPDATE/DELETE > implement RETURNING statement, so extending by ORDER and LIMIT would be > really useful. > All that with just one query. In this specific example, the ORDER BY > statement could be even omitted if we don't care how slots are > distributed between users. This probably came up in the discussion from back then as well, but what stops you from using a sub-select? UPDATE slots FROM (SELECT id FROM slots WHERE user IS NULL ORDER BY id LIMIT 1) AS available SET user='joe' WHERE id = available.id RETURNING *; Admittedly that's longer and would be slightly less efficient, but it is available now (and has been for a while) and it's still in one query. Also: > CREATE TABLE slots ( > id INTEGER UNIQUE NOT NULL, > user VARCHAR(32), > expires TIMESTAMP WITH TIMEZONE, > -- some other columns > ); I'd declare a primary key as what it is, not as some generic UNIQUE NOT NULL column ;) It won't make much difference in practice, but for example, that way it's intended use is immediately clear from the table definition if people look it up. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c9dcfe7678304776795795! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
