Postgres 15添加了子句NULLS NOT DISTINCT

参见：

• 创建具有空列的唯一约束

现在的解决方案非常简单：

ALTER TABLE example ADD CONSTRAINT foo
UNIQUE NULLS NOT DISTINCT (field1, field2, field3, field4, field5);

适用于14岁或以上的Postgres

您正在努力实现与现有Oracle和SQL Server实施的**兼容性。
因为Oracle在行存储中根本没有实现NULL值，所以它无法区分空字符串和NULL，所以在Postgres中使用空字符串（''）而不是NULL值不是更谨慎吗？
将唯一约束中包含的列定义为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 foo UNIQUE (field1, field2, field3, field4, field5)
);

注解

您在问题中演示的是一个唯一 * 索引*：

CREATE UNIQUE INDEX ...

不是你一直在说的"唯一"限制，而是有细微而重要的区别!

• PostgreSQL如何强制执行UNIQUE约束/它使用什么类型的索引？

我把它变成了一个实际的约束，就像问题的标题一样。
关键字ASC只是噪声，因为这是默认的排序顺序。
为简单起见，使用serial PK列，这完全是可选的，但通常比存储为text的数字更可取。

使用它

只需省略INSERT中的空/null字段：

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值，我建议使用唯一的 * index ，带有*COALESCE，就像您在选项（2）中提到的那样，并在最后一个示例中提供@wildplasser。
像@Rudolfo这样的数组**上的索引很简单，但是开销要大得多。在Postgres中数组处理不是很便宜，并且有一个类似于行（24字节）的数组开销：

• 在PostgreSQL中计算和节省空间

数组仅限于相同数据类型的列。如果某些列不是，则可以将所有列强制转换为text，但这通常会进一步增加存储要求。或者，可以对异类数据类型使用已知的行类型...
一个极端案例：所有值均为NULL的数组（或行）类型被视为相等（!），因此只能有一行的所有相关列均为NULL。可以是，也可以不是。如果要禁止所有列均为NULL：

• 一组列上的NOT NULL约束

第三种方法：使用IS NOT DISTINCT FROM而不是=来比较键列。（这可以利用候选 natural 键上的现有索引）示例（查看最后一列）

SELECT *
    , EXISTS (SELECT * FROM example x
     WHERE x.FIELD1 IS NOT DISTINCT FROM e.FIELD1
     AND x.FIELD2 IS NOT DISTINCT FROM e.FIELD2
     AND x.FIELD3 IS NOT DISTINCT FROM e.FIELD3
     AND x.FIELD4 IS NOT DISTINCT FROM e.FIELD4
     AND x.FIELD5 IS NOT DISTINCT FROM e.FIELD5
     AND x.ID <> e.ID
    ) other_exists
FROM example e
    ;

下一步就是把它放到一个触发器函数中，并在它上面放置一个触发器。（现在没有时间，也许以后会有）
下面是trigger-function（它还不完美，但看起来可以工作）：

CREATE FUNCTION example_check() RETURNS trigger AS $func$
BEGIN
    -- Check that empname and salary are given
    IF EXISTS (
     SELECT 666 FROM example x
     WHERE x.FIELD1 IS NOT DISTINCT FROM NEW.FIELD1
     AND x.FIELD2 IS NOT DISTINCT FROM NEW.FIELD2
     AND x.FIELD3 IS NOT DISTINCT FROM NEW.FIELD3
     AND x.FIELD4 IS NOT DISTINCT FROM NEW.FIELD4
     AND x.FIELD5 IS NOT DISTINCT FROM NEW.FIELD5
     AND x.ID <> NEW.ID
            ) THEN
        RAISE EXCEPTION 'MultiLul BV';
    END IF;

    RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER example_check BEFORE INSERT OR UPDATE ON example
  FOR EACH ROW EXECUTE PROCEDURE example_check();

UPDATE：一个唯一的索引有时候可以被 Package 到一个约束中（参见postgres-9.4 docs, final example），你确实需要发明一个标记值;我在这里使用了空字符串''。

CREATE UNIQUE INDEX ex_12345 ON example
        (coalesce(FIELD1, '')
        , coalesce(FIELD2, '')
        , coalesce(FIELD3, '')
        , coalesce(FIELD4, '')
        , coalesce(FIELD5, '')
        )
        ;

ALTER TABLE example
        ADD CONSTRAINT con_ex_12345
        USING INDEX ex_12345;

但是coalesce()上的“函数”索引在这个构造中是不允许的，唯一索引（OP的选项2）仍然有效，尽管：

ERROR:  index "ex_12345" contains expressions
LINE 2:  ADD CONSTRAINT con_ex_12345
             ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.
INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "ex_12345"

这实际上对我很有效：

CREATE UNIQUE INDEX index_name ON table_name ((
   ARRAY[field1, field2, field3, field4]
));

我不知道性能是如何受到影响的，但它应该接近理想状态（取决于postres中阵列的优化程度）

您可以创建一个规则，将ALL NULL值（而不是原始表）插入到分区（如partition_field1_nullable、partition_fiend2_nullable等）中。这样，您就可以仅在原始表中创建唯一索引（不带空值）。这将允许您仅向原始表插入非空值（具有唯一性），且不为空（并且相应地不唯一）值到“可空分区”。并且您可以仅对可空分区应用COALESCE或触发器方法，为了避免对原始表上的每个DML进行许多分散的部分索引和触发...