On Sat, 25 Sep 2010 12:32:55 +0200 Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote: > 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. Well, it's not that anything can stop me ;) It's just a suggestion. I think that: 1. UPDATE ... LIMIT approach is more obvious (= more clear for people to read) 2. as you said -- it's shorter and more efficient, even if it's just a little bit (one index scan less, if id was indexed). > 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. It was just a quick and dirty example, but of course you're right :) Regards, BK -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
