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. Let's say we need to reserve some kind of identifier for user. CREATE TABLE slots ( id INTEGER UNIQUE NOT NULL, user VARCHAR(32), expires TIMESTAMP WITH TIMEZONE, -- some other columns ); -- We have 4 identifiers to use: INSERT INTO slots VALUES (1); INSERT INTO slots VALUES (2); INSERT INTO slots VALUES (3); INSERT INTO slots VALUES (4); -- Now the clue: we want to reserve unused slot: UPDATE slots SET user='joe' WHERE user IS NULL ORDER BY id LIMIT 1 RETURNING *; -- Or another approach: reserve slot for 30 minutes: UPDATE slots SET expires=now()+INTERVAL '30 minutes' WHERE expires<now() ORDER BY id LIMIT 1 RETURNING *; Our query will return: 1. all data of the slot that have been reserved 2. no rows, if there is no free slot. 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. Comments are welcome. Best regards, -- Bartłomiej Korupczyński <bartek-fora@xxxxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
