skip to content

A PostgreSQL insert into a meeting-room bookings table fails with SQLSTATE 23P01: which kind of constraint rejected it, and how should the application respond?

level: juniorimportance: should knowfreq 34%

answer

  1. class 23, PostgreSQL-only suffix
  2. operators beyond equality
  3. temporal keys raise it too
  4. transaction aborted afterwards
  5. concurrent twin: 40P01

basics

~20 s

SQLSTATE 23P01 is exclusion_violation: an EXCLUDE constraint, or a PostgreSQL 18 WITHOUT OVERLAPS key, found an existing row that conflicts, such as the same room at an overlapping time. Match the code, report the slot as taken, roll back.

solid answer

~40 s

`23P01` is `exclusion_violation`, the class-23 integrity error raised by an `EXCLUDE` constraint and, in PostgreSQL 18, by a `PRIMARY KEY` or `UNIQUE` declared `WITHOUT OVERLAPS`, which is enforced the same way. The message reads `conflicting key value violates exclusion constraint "booking_no_overlap"`, and the `DETAIL` shows the new key and the existing key it clashed with: the same room at an overlapping time. The application should branch on the SQLSTATE and on the constraint name the error carries, never on message text, and turn it into a domain answer such as "that slot is taken". The transaction is now aborted, so it must `ROLLBACK` (or roll back to a savepoint) before anything else. Retrying the identical insert is pointless while the conflicting booking stands; only the concurrent `40P01` variant is worth a retry.

code

sql · 12 lines
sql
DO $$
DECLARE
  v_constraint text;
BEGIN
  INSERT INTO booking (room_id, during)
  VALUES (7, tstzrange('2026-10-06 09:30+00', '2026-10-06 10:30+00'));
EXCEPTION
  WHEN exclusion_violation THEN
    GET STACKED DIAGNOSTICS v_constraint = CONSTRAINT_NAME;
    RAISE NOTICE 'slot taken (constraint %)', v_constraint;
END;
$$;

go deeper

for a junior

Recall that 23P01 is exclusion_violation, raised when a new row conflicts with an existing one under an EXCLUDE constraint, and that you branch on the SQLSTATE and then roll back.

for a middle

Explain how 23P01 differs from 23505, what the constraint-name field gives you, and why the aborted transaction needs a ROLLBACK or a savepoint before any further statement.

for a senior

Show you have handled the concurrent case: exclusion checks can deadlock, so 40P01 is retried and resolves to 23P01, while 23P01 itself is a business answer, never a retry trigger.

for a principal

Weigh where conflict reporting lives, database errors mapped to domain responses versus pre-checks, and how one shared error-mapping layer keeps 23P01, 23505 and 40P01 handling consistent across services.

## What SQLSTATE 23P01 means PostgreSQL reports every error with a five-character **SQLSTATE**. Class `23` is *integrity constraint violation*, and its members tell you which kind of rule fired: | SQLSTATE | Condition name | Raised by | |---|---|---| | `23502` | `not_null_violation` | a NULL written to a `NOT NULL` column | | `23503` | `foreign_key_violation` | a missing parent row, or a parent still referenced | | `23505` | `unique_violation` | a `UNIQUE` or plain `PRIMARY KEY` duplicate | | `23514` | `check_violation` | a `CHECK` expression that returned false | | `23P01` | `exclusion_violation` | an `EXCLUDE` constraint, or an 18 `WITHOUT OVERLAPS` key | The `P` marks a PostgreSQL-specific code: the SQL standard has no exclusion constraints. An exclusion constraint such as `EXCLUDE USING gist (room_id WITH =, during WITH &&)` promises that no two rows have the same `room_id` **and** overlapping `during` ranges. A 23P01 therefore means the row you tried to write would sit beside an existing row for which every listed comparison is true. In a booking system: another reservation of the same room whose time range overlaps yours. PostgreSQL 18's temporal keys, such as `PRIMARY KEY (room_id, valid_at WITHOUT OVERLAPS)`, are enforced as exclusion constraints too, so their clashes also arrive as 23P01 rather than 23505. Error handling that only recognises the unique-violation code misses them. ## What the error carries A typical failure from `psql` looks like this: ``` ERROR: conflicting key value violates exclusion constraint "booking_no_overlap" DETAIL: Key (room_id, during)=(7, ["2026-10-06 09:30:00+00","2026-10-06 10:30:00+00")) conflicts with existing key (room_id, during)=(7, ["2026-10-06 09:00:00+00","2026-10-06 10:00:00+00")). ``` Beyond the text, the server sends structured fields that client drivers expose: - the **SQLSTATE** (`23P01`), the stable value to branch on; - the **schema, table and constraint name** (`PG_DIAG_CONSTRAINT_NAME` in libpq, `CONSTRAINT_NAME` in PL/pgSQL's `GET STACKED DIAGNOSTICS`), which tells you *which* rule fired when a table has several; - the `DETAIL` with both conflicting keys: useful in logs, but not something to parse or show raw to end users. ## How the application should respond 1. **Classify by SQLSTATE and constraint name**, not by message text. Messages are translated according to `lc_messages` and are reworded between releases. 2. **End the failed transaction.** The error aborts the current transaction, and every later statement fails with `25P02` (`in_failed_sql_transaction`, "current transaction is aborted, commands ignored until end of transaction block") until `ROLLBACK`. If the insert is one step of a larger unit of work you want to keep, set a `SAVEPOINT` before it and `ROLLBACK TO SAVEPOINT` on failure. 3. **Answer in domain terms.** Map it to a conflict response such as "room 7 is already booked 09:00 to 10:00", optionally with free alternatives found by querying the same table. 4. **Do not blindly retry.** The conflicting booking is committed; the same insert fails the same way until that booking is cancelled. Retry loops are for transient errors such as `40001` and `40P01`. ## Concurrency: the deadlock variant A B-tree unique index checks for duplicates atomically while it inserts. An exclusion constraint cannot: PostgreSQL inserts the new index entry, then runs a **separate index scan** for conflicting rows. If that scan finds a conflicting row whose transaction is still in progress, the backend waits for that transaction to end and checks again. When two sessions insert overlapping bookings at the same moment, each scan can find the other's uncommitted row, and each session waits for the other. The deadlock detector breaks the cycle by aborting one of them with `40P01` (`deadlock_detected`); the source comments call this harmless, because one of the two was bound to fail anyway. A booking endpoint should therefore: - treat `40P01`, like `40001`, as retryable; - expect the retry to come back as `23P01` once the winner has committed, and report it as a taken slot. ## What 23P01 does not mean - It is **not index corruption**: the GiST index behind the constraint did its job. - It is **not a duplicate key**: equality-only rules belong to `UNIQUE`; exclusion constraints use operators such as `&&` or `<>`. - It is **not fully absorbable by an upsert**: `INSERT ... ON CONFLICT DO NOTHING` can skip a row that would violate an exclusion constraint, but `ON CONFLICT DO UPDATE` cannot use one as its arbiter. Arbiter rules are the upsert topic's subject.

  • Why can two clients booking the same PostgreSQL room slot at the same instant get 40P01 instead of 23P01?
    An exclusion constraint is checked by an index scan after the new entry is inserted, not atomically like a B-tree unique check. If each session's scan finds the other's uncommitted row, each waits for the other; the deadlock detector aborts one with `40P01` and the other commits. Retry the `40P01`: the retry then fails cleanly with `23P01`.
  • Can INSERT ... ON CONFLICT absorb a PostgreSQL 23P01 from the booking constraint?
    Partly. `ON CONFLICT DO NOTHING` can skip a row that would violate an exclusion constraint, which suits idempotent imports. `ON CONFLICT DO UPDATE` cannot use an exclusion constraint as its arbiter, and no `ON CONFLICT` form accepts a deferrable constraint. For an interactive booking the explicit error is usually what you want: the user must hear the slot is gone.

  • 23P01 is just PostgreSQL's code for a duplicate key, the same as 23505.
  • Parse the English error message to find out which booking rule failed.
  • Retry the identical insert a few times; the overlap will clear itself.
  • After catching 23P01 the same transaction can carry on with its next statement.
  • An exclusion violation means the GiST index is corrupt and needs a REINDEX.