liveBook · Manning

Exclusion constraint

As explained in Just Use Postgres!

An exclusion constraint requires that at least one comparison between two rows on specified expressions and operators be false or null.

An exclusion constraint prevents two rows from coexisting when specified expressions compare with operators that all return true. At least one comparison must be false or null. section 2.3.1

For a shopping cart, constrain customer_id with = only among rows whose status is pending. This guarantees each customer has at most one pending order. section 2.6.1

Allow one pending order per customer

ALTER TABLE sales.orders ADD CONSTRAINT one_pending_order_per_customer EXCLUDE USING btree (customer_id WITH =) WHERE (status = 'pending'); The WHERE predicate limits enforcement to pending orders, while the B-tree index supports constraint validation. Updating the pending order to ordered permits a later pending cart. section 2.6.1

How Just Use Postgres! covers it

How deep it goes
Defines pairwise comparison semantics and demonstrates a conditional PostgreSQL constraint, but stops short of broader operator or expression patterns.
Examples
Uses a sales.orders table to enforce one pending order per customer while allowing completed orders.
What it assumes
Assumes familiarity with tables, ALTER TABLE, constraints, comparison operators, and basic PostgreSQL administration through psql.
What you can do after
Operate/configure PostgreSQL by adding a conditional exclusion constraint to an existing orders table.
How it connects
Connects constraints with database integrity, conditional predicates, operators, and B-tree indexes.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.