Postgres "RETURNING" tip

If you use Postgres, it has a language feature that is really helpful. When doing an INSERT, UPDATE, or DELETE, you can have the statement return data exactly like it had been a select on a single table.

The obvious, common use for that is returning an id after an insert:

INSERT INTO my_table (cols) VALUES (vals) RETURNING id;

But there are other uses that may not be as obvious. For example, suppose you are doing an update and want to track records that are changed in another table. You can do something like this:

WITH updated_ids AS (
  UPDATE my_table SET col = val WHERE condition RETURNING id
)
UPDATE tmp.tracker SET status = 'changed' WHERE rec_id IN (SELECT * FROM updated_ids);

The WITH block does the actual update and returns the ids that were changed as a table that you can then use to update your second table. Think about how you might do that in other SQL flavors where RETURNING is not available.

Neat, right?

Been using RETURNING for a while actually. Specially on inserts when I need to know the ID and such. It’s a really nice Postgres feature :wink:

I agree.