PostgreSQL 15 將可以對透過 UNIQUE 限制 NULL 的唯一性了

看到「Waiting for PostgreSQL 15 – Add UNIQUE null treatment option」這篇文章裡面提到 PostgreSQLUNIQUE 多加了一些功能進去:「Add UNIQUE null treatment option」。

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 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.

Reviewed-by: Maxim Orlov 
Reviewed-by: Pavel Borisov 
Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com

以往針對某個欄位下 UNIQUE 後,雖然同樣的值是無法 INSERT 進去,但 NULL 則是個例外,是可以塞多次進去的。

現在則是提供選項指定對 NULL 的解讀了;預設還是保留原來行為的 UNIQUE NULLS DISTINCT (把每個 NULL 都當作不同的值看待),特別指定後會變成 UNIQUE NULLS NOT DISTINCT (把每個 NULL 都當作一樣的值,進而被 UNIQUE 條件限制)。

在下一個版本的 PostgreSQL 15 就會出現這個功能了...

Leave a Reply

Your email address will not be published.