select rows ordered by timestamp, update first row and return it. all in single sql…
- User
select rows ordered by timestamp, update first row and return it. all in single sql command. postgres syntax
- Assistant
Here’s the idiomatic “one-liner” in PostgreSQL that
- picks the first row by timestamp,
- locks it so nobody else can touch it at the same time,
- updates it, and
- immediately returns the updated row
—all in a single SQL statement:
WITH picked AS ( SELECT ctid -- or the PK, e.g. id FROM my_table WHERE <optional-filter> -- e.g. status = 'pending' ORDER BY ts -- ts = your timestamp column LIMIT 1 FOR UPDATE SKIP LOCKED -- ← concurrency-safe ) UPDATE my_table AS t SET status = 'processing', -- your UPDATE list here updated_at = now() FROM picked p WHERE t.ctid = p.ctid -- or t.id = p.id RETURNING t.*; -- returns the updated rowExplanation
• Common-table expression
pickeddoes the “SELECT … ORDER BY ts LIMIT 1.FOR UPDATE SKIP LOCKED` locks that row and makes concurrent sessions skip it, so every worker gets a different row.• The main
UPDATE … FROMjoins the target table to that one picked row and changes whatever columns you need.•
RETURNING t.*(or a column list) immediately gives you the modified row back to the client.Why use
ctid?ctidis the physical row location and is always unique, so you can use it if you don’t have/want to expose a primary key in the CTE.
If you already have a primary key (sayid), just select/compare on that instead.Minimal example with a real table
-- sample table CREATE TABLE task_queue ( id serial PRIMARY KEY, payload text, ts timestamptz DEFAULT now(), status text DEFAULT 'pending' ); -- grab the oldest pending task, mark it "in_progress" and return it WITH picked AS ( SELECT id FROM task_queue WHERE status = 'pending' ORDER BY ts LIMIT 1 FOR UPDATE SKIP LOCKED ) UPDATE task_queue AS tq SET status = 'in_progress', started_at = now() FROM picked p WHERE tq.id = p.id RETURNING tq.*;That’s it—one statement, fully atomic, concurrency-safe, and it hands you back the updated row in one round-trip.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.