欢迎关注公众号, 持续发布PostgreSQL、openGauas/MogDB等PG系数据库干货文章。

分享几个NULL相关的案例，包括：NULL与操作符、NULL与索引、NULL与UNIQUE字段、NULL与外键引用、NULL与默认值

1.NULL与操作符

当使用"||"连接符和(+, -, *, /)算术运算符，以及(=, <, >, <=, >=, <>)比较操作符时，如果有NULL值可能会引起结果丢失。

为了更安全的使用NULL，在做连接时需要使用COALESCE函数，做比较运算时，(=, <>)可替换为 IS [NOT] DISTINCT FROM，动态语句传递变量时需要使用format函数，集合操作NOT IN可替换为NOT EXISTS。

2.NULL与索引

使用=或<>操作符时，运算结果不一定是布尔值，可能有NULL值参与运算，确保返回布尔值需进行如下替换：

替换后的IS NOT DISTINCT FROM会绕过索引，如果既需要保证安全的布尔值，又需要走索引，可参考如下的SQL等价改写。

改写前的SQL

SELECT * FROM foo
WHERE bar IS NOT DISTINCT FROM baz;

改写后的SQL

SELECT * FROM foo
WHERE (bar IS NULL AND baz IS NULL)
OR bar = baz;

3.NULL与UNIQUE字段

由于NULL != NULL，UNIQUE字段允许有多个NULL值，从版本15开始能控制UNIQUE字段是否允许有多个NULL值，语法如下：

UNIQUE NULLS DISTINCT 
UNIQUE NULLS NOT DISTINCT 

例如下面表字段column1只允许插入一个NULL值，使用UNIQUE NULLS NOT DISTINCT

CREATE TABLE tab1(
  column1 VARCHAR, 
UNIQUE NULLS NOT DISTINCT(column1) 
);

15之前的版本可以使用带where条件的表达式索引来实现：

CREATE UNIQUE INDEX ON tab1 ((column1 is null)) 
WHERE column1 IS NULL;

4.NULL与外键引用

如果外键引用多个字段时，可以使用num_nulls/num_nonnulls函数更好的定义约束，例如只允许外键引用字段列表里任意一个字段非空:

CHECK (num_nonnulls(column1, column2, column3) = 1);

参考示例如下：

CREATE TABLE companies (
id serial primary key,
name text
);
CREATE TABLE orders (
id serial primary key
);
CREATE TABLE users  (
id serial primary key,
name text
);
CREATE TABLE notifications (
id serial,
    company_id INT REFERENCES companies (id),
    order_id INT REFERENCES orders (id),
    user_id INT REFERENCES users (id)
);

notifications表添加约束：要求外键引用的三个字段列表有一个字段不为NULL

ALTER TABLE notifications
ADD CONSTRAINT notifications_reference
CHECK (num_nonnulls(company_id, order_id, user_id) = 1);

版本15对外键引用多字段设置on delete级联删除时还允许对单个字段设置NULL值或者默认值，示例如下：

 CREATE TABLE tab_pk ( 
 tid int, 
id int, 
 primary key (tid, id)
 );

CREATE TABLE tab_fk ( 
 tid int, 
id int, 
 fk_id_del_set_null int, 
 fk_id_del_set_default int default 0, 
foreign key (tid, fk_id_del_set_null) references tab_pk  on delete set null (fk_id_del_set_null), 
foreign key (tid, fk_id_del_set_default) references tab_pk on delete set default (fk_id_del_set_default) 
);

5.NULL与默认值

MySQL迁移过来的表字段上有default NULL的设置，迁移工具做了如下转化处理：

CREATE TABLE tab(action varbinary default NULL::varbinary);

增加了对NULL按实际类型转换的处理，这个默认值的处理对后续修改字段类型造成了影响。

ALTER TABLE tab 
ALTER COLUMN action TYPE varchar(8) USING action::varchar(8);

上面的语句通常情况下是可以正常执行，不过由于默认值类型转换导致修改会失败：

ERROR:  default for column "action" cannot be cast automatically to type varchar

上面的默认值定义无实际用处，但造成了字段类型修改时报错，因此也需要先调整处理默认值。

可以将默认值的调整和字段类型的修改放在一个事务里进行操作：

ALTER TABLE tab
ALTER COLUMN action DROP DEFAULT,
ALTER COLUMN action TYPE varchar(8) USING action::varchar(8),
ALTER COLUMN action SET DEFAULT NULL::varchar(8);