10 - INSERT, UPDATE, DELETE, and RETURNING Patterns

INSERT, UPDATE, DELETE, and RETURNING Patterns

PostgreSQL data modification is a transactional, set-based surface just like SQL Server, but the operational idioms are different in a few important places. PostgreSQL uses RETURNING instead of OUTPUT, supports UPDATE ... FROM and DELETE ... USING, and leans heavily on writable CTEs for move-and-capture workflows. Identity retrieval is usually not a separate function call at all because the inserted rows can be returned directly.

Scope

This note mirrors the SQL Server DML track with PostgreSQL equivalents. It covers insert patterns, searched and joined updates, searched and joined deletes, writable CTE move patterns, and TRUNCATE semantics.

• Insert patterns cover literal row inserts, INSERT ... SELECT, identity defaults, and RETURNING.
• Update and delete patterns cover searched DML plus PostgreSQL’s joined write forms.
• Row capture covers RETURNING and writable CTEs as the PostgreSQL equivalent to SQL Server OUTPUT.
• Table clearing covers TRUNCATE ... RESTART IDENTITY.

Insert Patterns

The main insert decision is where the incoming rowset comes from: literal values, another query, or a staged document or file boundary. Once the rowset exists, RETURNING is the safest way to prove what was actually written.

Literal inserts and INSERT ... SELECT

PostgreSQL supports both single-row and multi-row literal inserts, and it can return generated identity values or defaults immediately from the same statement.

Insert literal rows and return generated identity values

Use this pattern when a small fixed rowset must be inserted and the caller needs the generated keys or defaults immediately. It is typically triggered by application writes, test fixtures, and targeted repairs. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show the PostgreSQL equivalent of “insert rows and get back what the database assigned.”

Field	Source	Type	Meaning
position_id	identity column generated by PostgreSQL	integer	Generated surrogate key assigned during the insert.
symbol	inserted literal value	text	Symbol inserted into the target row.
status_code	column default	text	Default status assigned because the insert omitted the column.
quantity	inserted literal value	integer	Inserted numeric quantity.

This transaction inserts two rows into a temp table and returns the generated ids and defaults immediately.

BEGIN;
 
CREATE TEMP TABLE note10_positions (
    position_id integer GENERATED ALWAYS AS IDENTITY,
    symbol text NOT NULL,
    status_code text NOT NULL DEFAULT 'NEW',
    quantity integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT current_timestamp
) ON COMMIT DROP;
 
INSERT INTO note10_positions (symbol, quantity)
VALUES
    ('BNP.PA', 100),
    ('TTE.PA', 150)
RETURNING position_id, symbol, status_code, quantity;
 
ROLLBACK;

BEGIN
CREATE TABLE

position_id	symbol	status_code	quantity
1	BNP.PA	NEW	100
2	TTE.PA	NEW	150

INSERT 0 2
ROLLBACK

This is the normal PostgreSQL identity-retrieval pattern. There is no need for a separate SCOPE_IDENTITY()-style lookup when the statement can return the inserted rows directly.

Use INSERT ... SELECT when the source rowset already exists

Use this pattern when the inserted rows come from another query rather than literal values. It is typically triggered by staging, subset loads, and ETL fan-in. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show the standard set-based copy pattern.

Field	Source	Type	Meaning
symbol	gold.scores_daily.symbol	text	Source symbol inserted into the target table.
composite_rank	gold.scores_daily.composite_rank	integer	Latest rank copied from the source rowset.
composite_score	rounded gold.scores_daily.composite_score	numeric	Latest score copied into the target.

This transaction copies the top three latest Euro Stoxx 50 rows into a temp watchlist table and returns the inserted rows.

BEGIN;
 
CREATE TEMP TABLE note10_watchlist (
    symbol text,
    composite_rank integer,
    composite_score numeric(10,4)
) ON COMMIT DROP;
 
INSERT INTO note10_watchlist (symbol, composite_rank, composite_score)
SELECT
    symbol,
    composite_rank,
    ROUND(composite_score::numeric, 4)
FROM gold.scores_daily
WHERE score_date = (SELECT MAX(score_date) FROM gold.scores_daily)
  AND _index = 'euro_stoxx_50'
ORDER BY composite_rank
LIMIT 3
RETURNING symbol, composite_rank, composite_score;
 
ROLLBACK;

BEGIN
CREATE TABLE

symbol	composite_rank	composite_score
BNP.PA	1	0.5967
TTE.PA	2	0.4954
ENI.MI	3	0.4807

INSERT 0 3
ROLLBACK

This is the normal ETL-shaped insert. If the source query can produce the correct rowset, the insert should stay set-based and read straight from it.

Update and Delete Patterns

The write surface is still governed by the same rule as SQL Server: the statement modifies the qualifying set, not one row at a time. The important operational question is whether the predicate is local to the target table or depends on another rowset.

Searched and joined DML

Simple predicates use searched UPDATE or DELETE. Cross-rowset criteria use UPDATE ... FROM or DELETE ... USING.

Run a searched UPDATE when the predicate belongs entirely to the target table

Use this pattern when the qualification rule depends only on the target row itself. It is typically triggered by corrective edits, status transitions, and targeted patching. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show the simplest update form and its RETURNING output.

Field	Source	Type	Meaning
instrument_id	target temp table	integer	Identifier of the updated row.
symbol	target temp table	text	Symbol of the updated row.
status_code	target temp table	text	Current status code after the update.
status_note	target temp table	text	Updated status note returned from the modified row.

This transaction updates one symbol by predicate and returns the after-state directly from the modified row.

BEGIN;
 
CREATE TEMP TABLE note10_instr AS
SELECT * FROM demo_stc.instrument_state;
 
UPDATE note10_instr
SET status_note = 'Escalated for manual review'
WHERE symbol = 'SAP.DE'
RETURNING instrument_id, symbol, status_code, status_note;
 
ROLLBACK;

BEGIN
SELECT 5

instrument_id	symbol	status_code	status_note
2	SAP.DE	ACTIVE	Escalated for manual review

UPDATE 1
ROLLBACK

The RETURNING clause gives the after-image of the updated row immediately, which is why it replaces many SQL Server OUTPUT INSERTED... use cases.

Use UPDATE ... FROM when the new values come from another rowset

Use this pattern when the target rows must be updated from a second table, temp table, or derived source. It is typically triggered by enrichment, patch sets, and synchronized corrections. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show PostgreSQL’s joined update form.

Field	Source	Type	Meaning
instrument_id	target temp table	integer	Identifier of the updated instrument row.
symbol	target temp table	text	Instrument symbol matched to the patch table.
status_note	patched value from note10_status_patch	text	New note written into the target row.

This transaction updates two instrument rows by joining them to a patch table and returns the updated after-state.

BEGIN;
 
CREATE TEMP TABLE note10_instr AS
SELECT * FROM demo_stc.instrument_state;
 
CREATE TEMP TABLE note10_status_patch (
    symbol text PRIMARY KEY,
    new_note text NOT NULL
) ON COMMIT DROP;
 
INSERT INTO note10_status_patch VALUES
    ('MC.PA', 'Monitoring post-event drift'),
    ('TTE.PA', 'Earnings follow-up completed');
 
UPDATE note10_instr AS t
SET status_note = p.new_note
FROM note10_status_patch AS p
WHERE p.symbol = t.symbol
RETURNING t.instrument_id, t.symbol, t.status_note;
 
ROLLBACK;

BEGIN
SELECT 5
CREATE TABLE
INSERT 0 2

instrument_id	symbol	status_note
3	MC.PA	Monitoring post-event drift
5	TTE.PA	Earnings follow-up completed

UPDATE 2
ROLLBACK

As in SQL Server, joined updates must still be deterministic. If the source rowset can produce multiple source rows for one target row, the query needs to pre-aggregate or deduplicate first.

Delete rows and return what was removed

Use this pattern when rows should be removed by a direct predicate and the caller still needs the deleted row image. It is typically triggered by cleanup, queue-drain, and audit capture workflows. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show PostgreSQL’s delete-plus-capture surface.

Field	Source	Type	Meaning
instrument_id	deleted temp-table row	integer	Identifier of the removed row.
symbol	deleted temp-table row	text	Symbol of the removed row.
status_code	deleted temp-table row	text	Status of the row before deletion.

This transaction deletes one row by predicate and returns the deleted row immediately.

BEGIN;
 
CREATE TEMP TABLE note10_instr AS
SELECT * FROM demo_stc.instrument_state;
 
DELETE FROM note10_instr
WHERE symbol = 'NESN.SW'
RETURNING instrument_id, symbol, status_code;
 
ROLLBACK;

BEGIN
SELECT 5

instrument_id	symbol	status_code
4	NESN.SW	ACTIVE

DELETE 1
ROLLBACK

The deleted row image is available directly from RETURNING, which makes separate pre-delete select statements unnecessary in many workflows.

Use DELETE ... USING when another rowset defines what should be removed

Use this pattern when deletion depends on membership in another table or derived rowset. It is typically triggered by exclusion lists, synchronized cleanup, and staged deletes. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show PostgreSQL’s joined delete form.

Field	Source	Type	Meaning
instrument_id	deleted temp-table row	integer	Identifier of the removed instrument.
symbol	deleted temp-table row	text	Symbol matched by the delete-driving rowset.

This transaction deletes rows whose symbols appear in a separate remove-list table.

BEGIN;
 
CREATE TEMP TABLE note10_instr AS
SELECT * FROM demo_stc.instrument_state;
 
CREATE TEMP TABLE note10_remove(symbol text) ON COMMIT DROP;
 
INSERT INTO note10_remove VALUES ('ASML.AS'), ('TTE.PA');
 
DELETE FROM note10_instr AS t
USING note10_remove AS r
WHERE r.symbol = t.symbol
RETURNING t.instrument_id, t.symbol;
 
ROLLBACK;

BEGIN
SELECT 5
CREATE TABLE
INSERT 0 2

instrument_id	symbol
1	ASML.AS
5	TTE.PA

DELETE 2
ROLLBACK

DELETE ... USING is the PostgreSQL analogue to SQL Server’s joined delete syntax. It is still set-based and still benefits from keeping the delete-driving rowset clean and deterministic.

RETURNING and Composable DML

The biggest PostgreSQL-specific write feature in this note is that DML can feed later statements directly. RETURNING turns modified rows into a rowset, and writable CTEs let later statements consume that rowset in the same transaction.

Capture and move rows in one statement chain

This is the PostgreSQL equivalent of SQL Server OUTPUT ... INTO patterns.

Use a writable CTE to move deleted rows into an archive table

Use this pattern when a workflow must remove rows from one table and capture them into another table atomically. It is typically triggered by queue consumption, archival moves, and corrective migrations. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show PostgreSQL’s composable DML pattern.

Field	Source	Type	Meaning
instrument_id	deleted row from note10_instr	integer	Identifier of the moved row.
symbol	deleted row from note10_instr	text	Symbol moved into the archive table.
status_code	deleted row from note10_instr	text	Status copied into the archive table.
archived_at	archive table default	timestamptz	Archive timestamp assigned during the insert.
remaining_rows	follow-up count on note10_instr	bigint	Rows left after the move step.

This transaction deletes matching rows, inserts the deleted rowset into an archive table, returns the archive rows, and then proves the source table shrank.

BEGIN;
 
CREATE TEMP TABLE note10_instr AS
SELECT * FROM demo_stc.instrument_state;
 
CREATE TEMP TABLE note10_archive (
    instrument_id integer,
    symbol text,
    status_code text,
    archived_at timestamptz NOT NULL DEFAULT current_timestamp
) ON COMMIT DROP;
 
WITH moved AS (
    DELETE FROM note10_instr
    WHERE status_note ILIKE '%lifted%'
    RETURNING instrument_id, symbol, status_code
)
INSERT INTO note10_archive (instrument_id, symbol, status_code)
SELECT instrument_id, symbol, status_code
FROM moved
RETURNING instrument_id, symbol, status_code, archived_at;
 
SELECT COUNT(*) AS remaining_rows
FROM note10_instr;
 
ROLLBACK;

BEGIN
SELECT 5
CREATE TABLE

instrument_id	symbol	status_code	archived_at
3	MC.PA	ACTIVE	2026-04-19 01:01:57.034779+00
5	TTE.PA	ACTIVE	2026-04-19 01:01:57.034779+00

INSERT 0 2

remaining_rows
3

ROLLBACK

This is the composable DML pattern that replaces many SQL Server OUTPUT INTO designs. The deleted rows become a rowset first, and the next statement consumes that rowset immediately.

TRUNCATE Semantics

TRUNCATE is the fast “remove everything” operation, but it is chosen for its semantics, not just its speed. It does not behave like a predicate delete.

Clear the table and optionally restart identity values

In PostgreSQL, TRUNCATE ... RESTART IDENTITY resets identity sequences owned by the truncated table.

Restart identity values after a full table clear

Use this pattern when every row should be removed and the next load should reuse the identity sequence from the start. It is typically triggered by transient staging tables and rebuild workflows. The script is state-changing inside a transaction and rolls back at the end. Its purpose is to show the identity-reset behavior explicitly.

Field	Source	Type	Meaning
stage_id	identity column after truncate	integer	Identity value assigned after RESTART IDENTITY.
payload	inserted row after truncate	text	Payload stored in the restarted staging table.

This transaction inserts two rows, truncates the table with identity restart, inserts one new row, and shows that the id restarted at 1.

BEGIN;
 
CREATE TEMP TABLE note10_stage (
    stage_id integer GENERATED BY DEFAULT AS IDENTITY,
    payload text
) ON COMMIT DROP;
 
INSERT INTO note10_stage (payload)
VALUES ('alpha'), ('beta');
 
TRUNCATE note10_stage RESTART IDENTITY;
 
INSERT INTO note10_stage (payload)
VALUES ('gamma')
RETURNING stage_id, payload;
 
ROLLBACK;

BEGIN
CREATE TABLE
INSERT 0 2
TRUNCATE TABLE

stage_id	payload
1	gamma

INSERT 0 1
ROLLBACK

This is the semantic reason to choose TRUNCATE ... RESTART IDENTITY: clear the whole table and reset the generated key stream for the next load.

Practical Rules

Choose the DML form by the source rowset, the qualifying rule, and whether the modified rows need to be captured.

Need	PostgreSQL pattern	Why
Insert literal rows and get generated ids back	INSERT ... VALUES ... RETURNING	One statement inserts and captures the after-state.
Insert from another query	INSERT ... SELECT	Keeps the write path set-based.
Update from another rowset	UPDATE ... FROM	Joined update form for enrichment and patch sets.
Delete based on another rowset	DELETE ... USING	Joined delete form for staged cleanup.
Capture inserted, updated, or deleted rows	RETURNING	PostgreSQL analogue to SQL Server OUTPUT.
Move rows atomically between tables	Writable CTE plus RETURNING	Composable DML without triggers.
Clear all rows and reset identities	TRUNCATE ... RESTART IDENTITY	Fast full-table clear with key restart semantics.