Skip to content
The Chat Archive
o3-2025-04-16· OpenAI LMArena Human Preference 140K

select rows ordered by timestamp, update first row and return it. all in single sql…

  1. User

    select rows ordered by timestamp, update first row and return it. all in single sql command. postgres syntax

  2. Assistant

    Here’s the idiomatic “one-liner” in PostgreSQL that

    1. picks the first row by timestamp,
    2. locks it so nobody else can touch it at the same time,
    3. updates it, and
    4. 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 row
    

    Explanation

    • Common-table expression picked does 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 … FROM joins 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?

    ctid is 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 (say id), 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.