Update, Delete, and Upsert

Introduction

UPDATE changes existing rows; DELETE removes them. PostgreSQL adds RETURNING on both, and ON CONFLICT for upserts—insert-or-update in one statement. This chapter also covers safe habits: preview with SELECT, use transactions, and avoid accidental mass updates.

Prerequisites

• SELECT Queries Basics
• Seed data in users and posts
• Unique constraint on users.email from chapter 3

UPDATE Basics

Change one row by primary key:

UPDATE users
SET display_name = 'Ada Lovelace'
WHERE id = 1;

Update with expression:

UPDATE posts
SET title = title || ' (updated)'
WHERE id = 1;

Code explanation:

• || concatenates strings in PostgreSQL—MySQL often uses CONCAT()
• Always include a WHERE clause unless you intend to touch every row

Verify:

SELECT id, display_name FROM users WHERE id = 1;
SELECT id, title FROM posts WHERE id = 1;

UPDATE With RETURNING

See what changed without a follow-up query:

UPDATE users
SET is_active = FALSE
WHERE email = 'eve@example.com'
RETURNING id, email, is_active;

Useful in APIs returning the updated record to the client.

UPDATE From JOIN Pattern

PostgreSQL has no UPDATE t JOIN u syntax like MySQL. Use FROM:

UPDATE posts p
SET published_at = NOW()
FROM users u
WHERE p.user_id = u.id
  AND u.email = 'ada@example.com'
  AND p.published_at IS NULL;

Code explanation:

• FROM adds tables to the update scope—join condition goes in WHERE
• Compare MySQL updating and deleting UPDATE ... JOIN

JSONB and Array Updates

Merge JSON:

UPDATE posts
SET metadata = metadata || '{"reviewed": true}'::jsonb
WHERE id = 1;

Append a tag:

UPDATE posts
SET tags = array_append(tags, 'featured')
WHERE id = 1 AND NOT ('featured' = ANY (tags));

DELETE Basics

Remove specific rows:

DELETE FROM posts
WHERE published_at IS NULL
  AND user_id = 1;

Delete by id:

DELETE FROM users
WHERE id = 999;
-- 0 rows if id does not exist—no error

BEGIN;
SELECT id, title FROM posts WHERE published_at IS NULL;
-- If correct:
DELETE FROM posts WHERE published_at IS NULL;
-- Or ROLLBACK;
COMMIT;

DELETE With RETURNING

DELETE FROM users
WHERE is_active = FALSE
RETURNING id, email;

Captures deleted rows for audit logs or soft-delete migrations.

ON CONFLICT Upsert

PostgreSQL's native upsert replaces MySQL ON DUPLICATE KEY UPDATE.

Insert or skip:

INSERT INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Again')
ON CONFLICT (email) DO NOTHING;

Insert or update:

INSERT INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Lovelace')
ON CONFLICT (email) DO UPDATE
SET display_name = EXCLUDED.display_name;

Upsert with RETURNING:

INSERT INTO users (email, display_name)
VALUES ('new@example.com', 'New User')
ON CONFLICT (email) DO UPDATE
SET display_name = EXCLUDED.display_name
RETURNING id, email, display_name;

Code explanation:

• Conflict target (email) must match a unique index or constraint
• EXCLUDED is the proposed insert row
• DO UPDATE SET col = EXCLUDED.col overwrites columns on conflict

Upsert on Primary Key

INSERT INTO posts (id, user_id, title, body)
VALUES (1, 1, 'Replaced title', 'New body')
ON CONFLICT (id) DO UPDATE
SET
  title = EXCLUDED.title,
  body = EXCLUDED.body
WHERE posts.published_at IS NOT NULL;

Optional WHERE on DO UPDATE skips updates when conditions fail—handy for "do not overwrite published posts."

Safe Update and Delete Workflow

Example:

BEGIN;
 
SELECT id, title FROM posts WHERE user_id = 2 AND published_at IS NULL;
 
DELETE FROM posts
WHERE user_id = 2 AND published_at IS NULL;
 
COMMIT;

TRUNCATE vs DELETE

Use TRUNCATE only when you mean to empty the entire table—see chapter 3.

Practice Exercise

Deactivate inactive authors and archive their drafts:

FAQ

UPDATE without WHERE—what happens?

Every row in the table is updated. Always run a SELECT with the same predicate first.

ON CONFLICT requires what?

A unique or primary key constraint on the listed columns. ON CONFLICT DO NOTHING without a target works only when any unique violation should be ignored (PostgreSQL 9.5+).

EXCLUDED vs VALUES()?

PostgreSQL uses EXCLUDED.column. MySQL upsert uses VALUES(column)—same idea, different keyword.

Can DELETE return rows?

Yes—DELETE ... RETURNING. MySQL has no equivalent; select first or use triggers.

Upsert two unique keys?

You can only specify one conflict target per statement. Split logic or use a DO UPDATE that handles one constraint.

How do foreign keys affect DELETE?

Child rows may block deletes—delete or reassign children first, or use ON DELETE CASCADE on the foreign key—Constraints and Keys.