In PostgreSQL, add a RETURNING clause to an INSERT statement to get values from rows the statement actually inserts—or updates through ON CONFLICT DO UPDATE. It is a direct way to retrieve a generated ID or other defaulted value without issuing a separate lookup.
Get a generated ID in the insert statement
Use RETURNING after the insert’s VALUES clause. PostgreSQL returns the requested value as a result row:
INSERT INTO users (name)
VALUES ('Ada')
RETURNING id;If id is generated by a sequence or another column default, the returned value is the one assigned to that row. PostgreSQL’s data-modification documentation describes the clause, and its FAQ gives the same pattern for retrieving an inserted person’s ID.
This avoids a second query to look up the row. It is also safer than trying to infer which ID was generated: the result belongs to the row handled by this particular statement.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
#1 Best Overall
Choose columns or expressions to return
The RETURNING list uses output syntax like a SELECT list. You can request one or more target-table columns, all columns with *, aliases, or computed expressions. For an insert, unqualified column names refer to values in the new row.
Return several columns
INSERT INTO accounts (email)
VALUES ('ada@example.com')
RETURNING id, created_at;This is useful when the application needs both the generated key and a value supplied by a default, such as a creation timestamp.
Rank #2
Return a computed value
INSERT INTO measurements (raw_value)
VALUES (10)
RETURNING raw_value, raw_value * 1.8 + 32 AS fahrenheit;The expression is evaluated for the returned row, so the client can receive a derived value alongside the stored value. PostgreSQL documents the output-list rules in its RETURNING clause reference.
Handle one-row and multi-row inserts
A single-row insert normally produces one returned row for the row inserted. A multi-row VALUES insert, or an INSERT ... SELECT, can return one result row for each row successfully inserted. The output contains results for rows affected by the statement, not a copy of every input candidate.
INSERT INTO users (name)
VALUES ('Ada'), ('Grace')
RETURNING id, name;Read the result set from the database client just as you would read rows from a query. The command also reports its insert or update count; adding RETURNING makes the requested expressions available as result rows as well.
Use RETURNING with ON CONFLICT
With ON CONFLICT DO UPDATE, PostgreSQL can return values from rows that were updated because of a conflict, as well as rows inserted normally. For example:
INSERT INTO widgets (sku, name)
VALUES ('A-1', 'Widget')
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name
RETURNING id, sku, name;Here, EXCLUDED.name is the proposed value, while the returned columns describe the row resulting from the insert or update. PostgreSQL’s INSERT reference notes an important condition: if the conflicting row is locked but the DO UPDATE ... WHERE condition is false, that row is not updated and is not returned.
DO NOTHING does not return the conflicting row
With ON CONFLICT DO NOTHING, a conflicting candidate is skipped. Because no row was inserted or updated for that candidate, RETURNING produces no row for it. If some rows in a multi-row insert succeed and others conflict, the result includes the rows actually inserted, not the skipped conflicts.
Check privileges before using RETURNING
The caller needs INSERT privilege on the target table. Each column named in RETURNING also requires SELECT privilege. An ON CONFLICT DO UPDATE path additionally requires the relevant UPDATE privilege; columns read by conflict expressions or predicates can require SELECT privilege too. See PostgreSQL’s INSERT privilege requirements.
Account for trigger-modified row values
Values changed by applicable row-level BEFORE triggers can affect what INSERT ... RETURNING reports. In other words, the returned values need not be identical to the values originally supplied by the application. Check the trigger behavior documented for the PostgreSQL release you deploy, particularly when application logic depends on a trigger changing a returned field. The PostgreSQL 16 INSERT documentation describes the command’s behavior.
Consider portability when targeting other databases
PostgreSQL identifies RETURNING as an extension rather than standard SQL. Other database systems may offer a similar clause, a different syntax, or a client API for retrieving generated keys. If your application supports multiple database engines, verify the target engine’s current documentation and the driver’s generated-key behavior instead of assuming PostgreSQL syntax will work unchanged. See PostgreSQL’s INSERT reference.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →
