UNIQUE null treatment option

From: Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: UNIQUE null treatment option
Date: 2021-08-27 12:38:34
Message-ID: 84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


The SQL standard has been ambiguous about whether null values in
unique constraints should be considered equal or not. Different
implementations have different behaviors. In the SQL:202x draft, this
has been formalized by making this implementation-defined and adding
an option on unique constraint definitions UNIQUE [ NULLS [NOT]
DISTINCT ] to choose a behavior explicitly.

This patch adds this option to PostgreSQL. The default behavior
remains UNIQUE NULLS DISTINCT. Making this happen in the btree code
is apparently pretty easy; most of the patch is just to carry the flag
around to all the places that need it.

The CREATE UNIQUE INDEX syntax extension is not from the standard,
it's my own invention.

(I named all the internal flags, catalog columns, etc. in the
negative ("nulls not distinct") so that the default PostgreSQL
behavior is the default if the flag is false. But perhaps the double
negatives make some code harder to read.)

Attachment Content-Type Size
0001-Add-UNIQUE-null-treatment-option.patch text/plain 52.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message vignesh C 2021-08-27 12:39:16 Re: Added schema level support for publication.
Previous Message Robert Haas 2021-08-27 12:27:38 Re: Queries that should be canceled will get stuck on secure_write function