Re: UPDATE/DELETE with ORDER BY and LIMIT

On Sat, 25 Sep 2010 12:32:55 +0200
Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> 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