PostgreSQL在唯一约束中有多个可为空的列

小编典典

PostgreSQL在唯一约束中有多个可为空的列

sql

我们有一个遗留数据库架构，该架构具有一些有趣的设计决策。直到最近，我们仅支持Oracle和SQL
Server，但是我们试图添加对PostgreSQL的支持，这带来了一个有趣的问题。我已经搜索了Stack
Overflow和Internet的其余部分，但我不认为这种特殊情况是重复的。

对于唯一约束中的可为空的列，Oracle和SQL Server的行为相同，这实际上是在执行唯一检查时忽略为NULL的列。

假设我有下表和约束条件：

CREATE TABLE EXAMPLE
(
    ID TEXT NOT NULL PRIMARY KEY,
    FIELD1 TEXT NULL,
    FIELD2 TEXT NULL,
    FIELD3 TEXT NULL,
    FIELD4 TEXT NULL,
    FIELD5 TEXT NULL,
    ...
);

CREATE UNIQUE INDEX EXAMPLE_INDEX ON EXAMPLE
(
    FIELD1 ASC,
    FIELD2 ASC,
    FIELD3 ASC,
    FIELD4 ASC,
    FIELD5 ASC
);

在Oracle和SQL Server上，保留任何可为空的列NULL都将导致仅对非null列执行唯一性检查。因此，以下插入操作只能执行一次：

INSERT INTO EXAMPLE VALUES ('1','FIELD1_DATA', NULL, NULL, NULL, NULL );
INSERT INTO EXAMPLE VALUES ('2','FIELD1_DATA','FIELD2_DATA', NULL, NULL,'FIELD5_DATA');
-- These will succeed when they should violate the unique constraint:
INSERT INTO EXAMPLE VALUES ('3','FIELD1_DATA', NULL, NULL, NULL, NULL );
INSERT INTO EXAMPLE VALUES ('4','FIELD1_DATA','FIELD2_DATA', NULL, NULL,'FIELD5_DATA');

但是，由于PostgreSQL（正确地）遵循SQL标准，所以这些插入（以及其中任何一个值是NULL的任何其他组合）都不会引发错误，并且可以正确插入。不幸的是，由于我们的旧模式和支持代码，我们需要PostgreSQL与SQLServer和Oracle表现相同。

我知道以下堆栈溢出问题及其答案：创建具有nullcolumn的唯一约束。据我了解，有两种解决此问题的策略：

在可为空的列同时为NULL和的情况下，创建描述索引的部分索引NOT NULL（这会导致部分索引的数量呈指数增长）
COAELSCE与索引中可为空的列上的哨兵值一起使用。

（1）的问题在于，我们需要创建的部分索引的数量与我们想要添加到约束中的每个其他可空列成指数增长（如果我没有记错的话，则为2 ^
N）。（2）的问题是哨兵值减少了该列的可用值数量，并减少了所有潜在的性能问题。

我的问题：这是该问题的仅有的两种解决方案吗？如果是这样，那么在此特定用例之间它们之间的权衡是什么？一个好的答案将讨论每种解决方案的性能，可维护性，PostgreSQL如何在简单的SELECT语句中利用这些索引以及任何其他“陷阱”或需要注意的事情。请记住，5个可为空的列仅作为示例；我们的架构中有一些表格，最多可以有10个表格（是的，每次看到它我都会哭，但这就是事实）。

阅读 235

2021-03-23

共1个答案

小编典典

您正在努力与现有的 Oracle 和 SQL Server 实现兼容。
这是一个比较三个涉及的RDBS的物理行存储格式的演示。

由于Oracle根本不实现NULL行存储中的值，因此NULL无论如何也无法分辨出空字符串与字符串之间的区别。因此，对于这种
特殊的用例，使用空字符串（''）代替NULLPostgres中的值是否明智？ __

将唯一约束中包含的列定义为NOT NULL DEFAULT ''，已解决的问题：

CREATE TABLE example (
   example_id serial PRIMARY KEY
 , field1 text NOT NULL DEFAULT ''
 , field2 text NOT NULL DEFAULT ''
 , field3 text NOT NULL DEFAULT ''
 , field4 text NOT NULL DEFAULT ''
 , field5 text NOT NULL DEFAULT ''
 , CONSTRAINT example_index UNIQUE (field1, field2, field3, field4, field5)
);

笔记

您在问题中展示的是 唯一索引 ：
```
CREATE UNIQUE INDEX ...
```

而不是您一直在谈论的 唯一约束 。有细微，重要的差异！

* [PostgreSQL如何执行UNIQUE约束/它使用什么类型的索引？](https://stackoverflow.com/questions/9066972/how-does-postgresql-enforce-the-unique-constraint-what-type-of-index-does-it-u/9067108#9067108)

我将其更改为实际约束，就像您将其作为帖子主题一样。

关键字ASC只是noise，因为这是默认的排序顺序。我把它丢了。
serial为简单起见，使用PK列是完全可选的，但通常比存储为的数字更好text。

使用它

只需从中忽略空/空字段INSERT：

INSERT INTO example(field1) VALUES ('F1_DATA');
INSERT INTO example(field1, field2, field5) VALUES ('F1_DATA', 'F2_DATA', 'F5_DATA');

重复任何这些插入操作都会违反唯一约束。

或者， 如果您坚持要省略目标列（这在持久化INSERT语句中有点反模式）：
或者对于需要列出所有列的批量插入：

INSERT INTO example VALUES
  ('1', 'F1_DATA', DEFAULT, DEFAULT, DEFAULT, DEFAULT)
, ('2', 'F1_DATA','F2_DATA', DEFAULT, DEFAULT,'F5_DATA');

或者简单地：

INSERT INTO example VALUES
  ('1', 'F1_DATA', '', '', '', '')
, ('2', 'F1_DATA','F2_DATA', '', '','F5_DATA');

或者，您可以编写一个BEFORE INSERT OR UPDATE转换NULL为的触发器''。

替代解决方案

如果您需要使用实际的NULL值，则建议使用唯一索引， COALESCE
就像您在选项（2）中提到的那样，并在最后一个示例中提供@wildplasser。

像@Rudolfo这样的数组
上的索引很简单，但是要昂贵得多。数组处理在Postgres中并不是很便宜，并且存在类似于行（24字节）的数组开销

数组仅限于相同数据类型的列。您可以将所有列都强制转换为，text如果不是，则通常会进一步增加存储需求。或者您可以将众所周知的行类型用于异构数据类型…

一个极端的情况：具有所有NULL值的数组（或行）类型被视为相等（！），因此只能有1行且所有涉及的列均为NULL。可能会或可能不会符合要求。

2021-03-23