Re: UNIQUE null treatment option
Peter Eisentraut <peter.eisentraut@enterprisedb.com>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2021-12-29T10:06:41Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add UNIQUE null treatment option
- 94aa7cc5f707 15.0 landed
Attachments
- v2-0001-Add-UNIQUE-null-treatment-option.patch (text/plain) patch v2-0001
Here is a rebased version of this patch.
On 27.08.21 14:38, Peter Eisentraut wrote:
> 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.)