Re: UPDATE/DELETE with ORDER BY and LIMIT - Mailing list pgsql-general

From Bartlomiej Korupczynski
Subject Re: UPDATE/DELETE with ORDER BY and LIMIT
Date
Msg-id 20100929001628.f8547c49.bartek-sql@klolik.org
Whole thread Raw
In response to Re: UPDATE/DELETE with ORDER BY and LIMIT  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
List pgsql-general
On Sat, 25 Sep 2010 12:32:55 +0200
Alban Hertroys <dalroi@solfertje.student.utwente.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'sstill 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
inpractice, but for example, that way it's intended use is immediately clear from the table definition if people look
itup. 

It was just a quick and dirty example, but of course you're right :)


Regards,
BK

pgsql-general by date:

Previous
From: Jeff Davis
Date:
Subject: Re: Exclusion constraint issue
Next
From: Bartlomiej Korupczynski
Date:
Subject: How to handle results with column names clash