liveBook · Manning

Nulls not distinct

As explained in PostgreSQL Mistakes and How to Avoid Them

NULLS NOT DISTINCT is a PostgreSQL uniqueness option that treats null values as equal for uniqueness enforcement.

A composite unique index normally treats NULL values as distinct. An inventory table keyed by product, warehouse, and area can therefore accept several rows whose area is NULL, even when those rows represent the same logical record. section 2.8

The upsert that inserts duplicates

With a default unique index on (product_id, warehouse_id, area), an upsert containing area = NULL can insert another row instead of updating the existing one. Repeating the operation with quantities 5 and 7 produces separate NULL-area rows, creating data-integrity problems. section 2.8

PostgreSQL 15 adds NULLS NOT DISTINCT to unique indexes. This option makes NULL values compare as equal for uniqueness enforcement, so matching non-NULL key values identify the same row during ON CONFLICT processing. section 2.8

DELETE FROM erp.inventory WHERE quantity = 7;
DROP INDEX erp.inventory_product_id_warehouse_id_area_idx;
CREATE UNIQUE INDEX ON erp.inventory (product_id, warehouse_id, area)
NULLS NOT DISTINCT;

Rebuild the unique index with NULLS NOT DISTINCT, then retry the upsert; the existing NULL-area row is updated rather than duplicated. section 2.8

Although this option fixes the immediate upsert behavior, nullable columns in composite unique keys remain a questionable design. Prefer a meaningful sentinel value such as common_area or restructure the schema when that better represents the data. section 2.11 section 2.8

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Explains default NULL uniqueness, demonstrates duplicate upserts, and shows index recreation, while stopping short of internal index mechanics.
Examples
Demonstrates an inventory table, repeated NULL-area upserts, table output, and a rebuilt unique index.
What it assumes
Assumes SQL, unique indexes, composite keys, and ON CONFLICT, while clarifying PostgreSQL's NULL uniqueness behavior.
What you can do after
Operate/configure it by rebuilding a PostgreSQL unique index with NULLS NOT DISTINCT for correct NULL-containing upserts.
How it connects
Connects NULL values with composite unique keys, unique indexes, ON CONFLICT, upserts, and schema design.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.