看到「Waiting for PostgreSQL 15 – Add UNIQUE null treatment option」這篇文章裡面提到 PostgreSQL 對 UNIQUE
多加了一些功能進去:「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 OrlovReviewed-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 就會出現這個功能了...
Mentions
Mentions