CRUD and RETURNING

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind CRUD and RETURNING
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

CRUD means create, read, update, and delete. PostgreSQL's `RETURNING` clause returns rows changed by INSERT, UPDATE, or DELETE without an extra SELECT. An UPDATE or DELETE without `WHERE` can affect the entire table, so develop the habit of using transactions and checking affected row counts.

Connect it to a real scenario

Insert two users and update one display name. Instead of guessing the generated identity, obtain it with `RETURNING user_id`. Before deleting, verify the filter with SELECT and consider whether the domain actually requires soft deletion.

Read generated values with `RETURNING`
Verify filters before UPDATE/DELETE
Have the application check affected row counts

Try the working example

sql

INSERT INTO app.users (email, display_name)
VALUES
  ('a@example.com', 'Aye Aye'),
  ('b@example.com', 'Bo Bo')
RETURNING user_id, email;

UPDATE app.users
SET display_name = 'Aye Thiri'
WHERE email = 'a@example.com'
RETURNING *;

DELETE FROM app.users
WHERE email = 'b@example.com'
RETURNING user_id;

You should see

Each insert, update, and delete returns the row it affected.

5-minute try-it

Insert three tutorials, then publish one unpublished row and set `published_at = now()`.

One important caution

Accidental UPDATE/DELETE without WHERE is a common data incident. Never experiment against production without a transaction and recoverable backups.

PostgreSQL — Data Manipulation — PostgreSQL Global Development Group

Easy traps

Accidental UPDATE/DELETE without WHERE is a common data incident. Never experiment against production without a transaction and recoverable backups.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Insert three tutorials, then publish one unpublished row and set `published_at = now()`.

You'll know it worked when: Each insert, update, and delete returns the row it affected.