PostgreSQL - The RETURNING Clause

The RETURNING clause allows one to return the rows that one inserts, updates, or deletes. This is a really neat/useful feature as it can prevent the need to run multiple queries.

I was surprised that I didn't already know about it but then I discovered that it is only supported by Oracle and PostgreSQL, and not MySQL/MariaDB.

Setup

If you want to run any of the examples below, you may wish to run the following queries to setup your database:

CREATE TYPE task_status AS ENUM ('open', 'locked', 'completed');

CREATE TABLE tasks (
    id uuid NOT NULL,
    name varchar(255),
    payload jsonb NOT NULL,
    status task_status,
    PRIMARY KEY (id)
);

Also, we will need to insert some tasks to work with:

INSERT INTO tasks (id, name, payload, status) VALUES
('947b4005-d7e7-49f7-87bc-bd6315122c6a', 'task1', '{"name" : "task1"}', 'open'),
('947b4050-0259-411e-9b7f-419914a09768', 'task2', '{"name" : "task2"}', 'open'),
('947b4050-0329-4518-93bd-c85bbc821529', 'task3', '{"name" : "task3"}', 'open'),
('947b4050-032f-4a11-bdc8-d3952439e627', 'task4', '{"name" : "task4"}', 'open'),
('947b4050-0334-4865-885f-48d32f195414', 'task5', '{"name" : "task5"}', 'open')
;

Examples

Return Deleted Rows

This simple case of adding RETURNING * allows you to find out what rows you deleted.

DELETE FROM tasks
  WHERE status = 'completed'
  RETURNING *;

For reproducing this, you may wish to run the query:

UPDATE tasks 
SET status='completed' 
WHERE status='open' ;

Return Updated Rows

The following will update any open tasks to mark them as locked, and return the ones it managed to do this to:

UPDATE tasks 
SET status='locked' 
WHERE status='open' 
RETURNING *;

Limited Update

The above example isn't very useful as it will update all the tasks and return them. If we want to limit ourselves to just 1 task, then we would need to do the following:

UPDATE "tasks" 
SET status = 'locked' WHERE id IN (
    SELECT id FROM tasks where status='open' LIMIT 1 FOR UPDATE SKIP LOCKED
)
RETURNING *;

We have to use a subquery because we cannot just apply a LIMIT clause to an UPDATE statement.

Alternatively, if you wish to process a couple of tasks at a time, just change the limit like so:

UPDATE "tasks" 
SET status = 'locked' WHERE id IN (
    SELECT id FROM tasks where status='open' LIMIT 3 FOR UPDATE SKIP LOCKED
)
RETURNING *;

The FOR UPDATE SKIP LOCKED looks ugly/complicated, but it prevents one from having that row edited whilst the query is in progress, and it also ensures that nothing else is reads the task before we manage to set the status to locked. More info.

More examples [1][2]

Returning Inserts

One can use the RETURNING clause on INSERT statements as well. This may seem pointless, as one would expect to already know the information one is inserting but this is useful when there are default values, such as when one makes use of DEFAULT CURRENT_TIMESTAMP, or if one is using the SERIAL type for one's identifiers instead of UUIDs.

E.g.

CREATE TABLE jobs (
    id serial NOT NULL,
    name varchar(255),
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO jobs (name) 
VALUES 
    ('collect-underpants'), 
    ('?'), 
    ('profit') 
RETURNING *;

This returns:

 id |        name        |         created_at         
----+--------------------+----------------------------
  1 | collect-underpants | 2021-09-25 12:36:24.826055
  2 | ?                  | 2021-09-25 12:36:24.826055
  3 | profit             | 2021-09-25 12:36:24.826055
(3 rows)

References

• Stack Overflow - Mysql returning clause equivalent
• dba.stackexchange.com - Postgres UPDATE ... LIMIT 1
• LornaJane.net - The RETURNING Keyword in PostgreSQL