PostgreSQL的空串、空值对唯一性约束的影响

一、现象及原因

今天在PostgreSQL又遇到一个现象，应用代码报错：“ERROR: duplicate key value violates unique constraint ...”。最后查明，这是由于PostgreSQL把空串('')、空值(NULL)当作不同的值，从而影响了那些没有申明为NOT NULL字段的unique约束。

二、问题重现

为重现问题，创建测试表如下：

create table tt1
(
  id int not null,
  name varchar(20),
  unique (name)
);

然后向该表插入测试记录：

pgtest=# insert into tt1 values(1, 'OK');
INSERT 0 1
pgtest=# insert into tt1 values(2, null);
INSERT 0 1
pgtest=# insert into tt1 values(3, null);
INSERT 0 1
pgtest=# insert into tt1 values(4, '');
INSERT 0 1
pgtest=# insert into tt1 values(5, '');
ERROR:  duplicate key value violates unique constraint "tt1_name_key"
描述:  Key (name)=() already exists.

可见，在PostgreSQL中空值(NULL)与空串(‘’)并非等同，且NULL不影响unique约束。再查找PostgreSQL资料，果然如此。

在Oracle中，对于varchar类型，空值(NULL)与空串(‘’)总是被判定相同，所以多处代码对此不加区分，结果总是符合预期。但移植到PostgreSQL后就会产生问题，除了已提到的影响unique约束外，查询时如果条件是isnull 或=''也会出现查不全的现象。

三、解决方法

常见的解决方法有两种。其一是最彻底的方法即全面检查应用代码，将赋空串的地方全改为赋空值；但此方法对已有项目来说工作量巨大，难以实施下去，只能在新项目中推广。其二属于变通方法即对表创建触发器，在插入或修改之前将约束字段的空串自动修改为空值。示例脚本：

CREATE OR REPLACE FUNCTION TF_BLANK2NULL() RETURNS TRIGGER
AS $TFHS_BLANK2NULL$
BEGIN
   IF (NEW.name='') THEN NEW.name:=NULL; END IF;
   RETURN NEW;
END;
$TFHS_BLANK2NULL$ LANGUAGE PLPGSQL;

CREATE TRIGGER TG_BLANK2NULL BEFORE INSERT OR UPDATE ON tt1  FOR EACH ROW EXECUTE PROCEDURE TF_BLANK2NULL();

四、注意事项

• 如果约束字段已经申明为NOT NULL，则无需担心；
• 触发器必须是BEFORE而不能是AFTER，另外不必考虑DELETE操作；
• 查询条件中用“=''”在PostgreSQL是可行的，但在Oracle中无效；
• 以上unique约束针对单一字段，但同样可推广到多个字段联合时的情况。