INSERT, UPDATE, DELETE & UPSERT

Changing data, RETURNING the changed rows, UPDATE ... FROM, and INSERT ... ON CONFLICT.

PostgreSQL extends the standard data-modification statements in two very practical ways: RETURNING sends back the rows that were changed (including generated ids), and ON CONFLICT turns an insert into an upsert.

Modifying data

INSERT ... RETURNING emp_id returns the id the database generated, with no second query.

UPDATE ... RETURNING shows the new values; the old salary is recomputed here only for display.

UPDATE ... FROM joins another table to decide which rows to change.

ON CONFLICT (emp_id, project_id) DO UPDATE adds the new hours to the existing row; EXCLUDED is the row that was proposed.

DELETE ... RETURNING lists exactly what was removed — useful for audit logs and queues.

Notes

UPDATE employees SET salary = salary * 1.1; -- no WHERE UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 3;

Without WHERE every row changes. Run the WHERE as a SELECT first, or do it inside BEGIN ... and check the row count before COMMIT.

How do you get the id of a newly inserted row in one statement?

INSERT ... RETURNING id;

What is EXCLUDED in ON CONFLICT DO UPDATE?

The row that was proposed for insertion; e.g. SET hours = EXCLUDED.hours takes the new value, and table.hours refers to the existing row.