Returning Data After an Insert in PostgreSQL

You’ve successfully inserted one or more rows into a table using a standard INSERT statement in PostgreSQL. Now, suppose that your schema contains an auto-generated UUID or SERIAL column:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE foo
(
	id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
	bar VARCHAR NOT NULL
);

You want to retrieve the auto-generated IDs for your newly inserted rows. To do that, you can simply use the RETURNING clause, like so:

INSERT INTO foo VALUES (DEFAULT, 'a'), (DEFAULT, 'b'), (DEFAULT, 'c')
RETURNING id;

Here’s one possible result:

Now, you don’t actually have to return the ID or a key—you can return the values under any column:

INSERT INTO foo VALUES (DEFAULT, 'e'), (DEFAULT, 'f'), (DEFAULT, 'g')
RETURNING bar;

Result:

You can even use aliases:

INSERT INTO foo VALUES (DEFAULT, 'e'), (DEFAULT, 'f'), (DEFAULT, 'g')
RETURNING bar AS baz;

Result:

Alternative: Top-N Query (with Serial Primary Keys)

If the table in question uses a SERIAL primary key, then you can retrieve values for the last N inserted rows by writing a separate Top-N query with a LIMIT clause equal to N:

SELECT id
FROM foo
ORDER BY id DESC
LIMIT 3;

Again, this only works if your IDs form a discrete sequence, which is the case with the SERIAL auto-incrementing integer type. Furthermore, note that this option requires writing two separate queries, whereas PostgreSQL’s RETURNING clause allows you to return data after an insert with just one query. The RETURNING syntax is more convenient if you need to use the returned IDs or values in a subsequent query.