Soft delete with a partial unique index
Keep deleted rows but still enforce unique emails among active rows using a partial unique index.
- SQL
- GreenSnippets
- MIT
ALTER TABLE users ADD COLUMN deleted_at timestamptz;
CREATE UNIQUE INDEX users_email_active_key
ON users (lower(email))
WHERE deleted_at IS NULL;
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
-- "Delete" a user
UPDATE users SET deleted_at = now() WHERE id = 42 AND deleted_at IS NULL;Example / expected output
-- The same email can exist once active and any number of times deleted.How it works
A plain UNIQUE (email) blocks re-registering a deleted user's email. A partial unique index applies only to rows where deleted_at IS NULL. Make sure every query that should ignore deleted rows filters on it, or hide them behind a view.
This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.
Related snippets
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
Walk a tree with a recursive CTE
Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.
- SQL
Pivot rows into columns with FILTER
Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.
- SQL
Query JSONB and index it with GIN
Filter on JSONB fields with the containment operator and make it fast with a GIN index.