How to model soft deletes in PostgreSQL without breaking unique constraints?

The problem

I add deleted_at TIMESTAMP. Now a user can't re-create a row with the same unique key as a soft-deleted one. Partial index? Nullable columns? What's clean?

The solution

No comments have been posted yet, so there isn't yet a converged answer to summarize. Here's a partial answer based on common, well-established PostgreSQL practice for this exact problem, but treat it as a starting point to validate against your own constraints, not a definitive fix:

What's ruled out

• A plain UNIQUE constraint on the key column(s) alone doesn't work once you add deleted_at, since Postgres will still enforce uniqueness across soft-deleted and live rows.
• Just adding deleted_at to a composite unique constraint (e.g. UNIQUE(key, deleted_at)) is fragile: NULL values are treated as distinct in standard unique constraints, so it can work for "all active rows have deleted_at = NULL", but it's easy to get subtly wrong if you ever set deleted_at to a non-null sentinel or allow multiple deletes.

Likely cleanest approach: partial unique index

Enforce uniqueness only among "live" (not-deleted) rows using a partial index:

CREATE UNIQUE INDEX uniq_active_key
ON your_table (key_column)
WHERE deleted_at IS NULL;

This lets you have unlimited soft-deleted rows with the same key_column, while still guaranteeing no two active rows collide. This is the standard pattern for this problem.

Things to double check before adopting it

• Make sure your ORM/migration tool supports partial indexes cleanly (some don't generate them by default).
• If the key is composite, include all relevant columns: WHERE deleted_at IS NULL still applies, just extend the column list.
• Consider whether you actually need to preserve history of soft-deleted rows with the same key, or whether an alternative (moving deleted rows to an archive table) suits your case better — that avoids the constraint problem entirely but changes your query model.

Since there's no further discussion yet to confirm edge cases (e.g., behavior with concurrent inserts/deletes, or NULL-key handling), it's worth testing this against your actual schema and expected concurrency patterns before finalizing.