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.
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!