本期分享几个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 DISTINCT FROM |
| = | IS NOT DISTINCT FROM |
替换后的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);
复制