Postgresql中null值和空字符串举例详解


    目录
  • NULL和空字符串不同数据库的表现
  • 判断和NULL值冲突的例子
  • 如果我们在a和b上建一个NULLS NOT DISTINCT的约束，可以实现上述要求吗？
  • 使用Range Types实现目的
  • 总结 

    NULL和空字符串不同数据库的表现
    null和空字符串在不同的数据库中表现不一样，找了一张图，可以很清晰的对比了解。
    
    首先null不是一个空字符串，也不是一个为零的值，上图，Oracle将NULL和空字符串都视为NULL。PostgreSQL将NULL视为NULL，将空字符串视为空字符串。与PostgreSQL类似，SQL Server也将NULL视为NULL，将空字符串视为空字符串。
    Oracle需要一个字节来存储NULL。然而，PostgreSQL和SQL Server都不需要任何空间来存储NULL。
    在Oracle中，存在唯一约束的列可以存储任意数量的NULL项和空字符串。
    在SQL Server中，在一个列上存在唯一约束时，只允许插入一个NULL和一个空字符串。
    在PostgreSQL中，允许插入多个类似于Oracle的NULL值。与SQL Server类似，PostgreSQL的列存在唯一约束时，只允许一个空字符串。
    oracle和SQL Server不一一举例，pg中例子如下：
    
#pg中，列b虽然有唯一越苏，但可以插入多个null值
postgres=# create table tab(a bigint,b text, UNIQUE(b));
CREATE TABLE
postgres=# insert into tab values(1,null);
INSERT 0 1
postgres=# insert into tab values(1,null);
INSERT 0 1
#但是不能插入多个空字符串
postgres=# insert into tab values(2,'');
INSERT 0 1
postgres=# insert into tab values(2,'');
ERROR:  duplicate key value violates unique constraint "tab_b_key"
DETAIL:  Key (b)=() already exists.

    从PostgreSQL 15开始，我们可以使用NULLS NOT DISTINCT子句来防止null值被处理为DISTINCT。这可以防止我们在具有唯一约束的列中插入多个空值。
    支持以下可选项：
    
  • NULLS NOT DISTINCT null也被认为是等同于null的, unique字段不能存多个null值;
  • NULLS DISTINCT 保持向后兼容, unique字段可以存储多个null值.

    创建索引也支持该选项
    
CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;

    看一下例子：
    
#创建测试表，插入null值，可见只能插入一个，再插违反唯一约束
postgres=# CREATE TABLE tab(a text, b text, UNIQUE NULLS NOT DISTINCT(a));
CREATE TABLE
postgres=# insert into tab values (null);
INSERT 0 1
postgres=# insert into tab values (null);
ERROR:  duplicate key value violates unique constraint "tab_a_key"
DETAIL:  Key (a)=(null) already exists.

    判断和NULL值冲突的例子
    有以下一张表，我们要确保a和b的组合是唯一的，并遵守以下两个规则:
    
  • 如果b为NULL，那么a不能有重复的值插入
  • 如果b不为NULL,是其它的不同值，那么a可以插入重复的值

    
CREATE TABLE tab (
   a integer NOT NULL,
   b integer
);

-- 可插入
INSERT INTO tab VALUES (1, NULL);
-- 由于规则1，不能插入，因为b为NULL
INSERT INTO tab VALUES (1, 2);
-- 可以插入
INSERT INTO tab VALUES (5, 2);
-- 可以插入，遵守规则2
INSERT INTO tab VALUES (5, 3);
-- 不能插入，违反规则1
INSERT INTO tab VALUES (5, NULL);

    如果我们在a和b上建一个NULLS NOT DISTINCT的约束，可以实现上述要求吗？
    
#创建唯一索引，插入数据测试，NULLS NOT DISTINCT不符合我们想要的效果
create unique index idx_tab_a_b on tab (a,b) NULLS NOT DISTINCT;
postgres=# INSERT INTO tab VALUES (1, NULL);
INSERT 0 1
postgres=# INSERT INTO tab VALUES (1, 2);
INSERT 0 1
postgres=# INSERT INTO tab VALUES (5, 2);
INSERT 0 1
postgres=# INSERT INTO tab VALUES (5, 3);
INSERT 0 1
postgres=# INSERT INTO tab VALUES (5, NULL);
INSERT 0 1

    使用Range Types实现目的
    
#Range Types几个实例，[]表示闭区间，包括边界，()表示开区间，不包括边界
SELECT '[2022-09-15 00:00:00,2022-09-16 00:00:00)'::tsrange;
 
                    tsrange                    
═══════════════════════════════════════════════
 ["2022-09-15 00:00:00","2022-09-16 00:00:00")
 
SELECT '[-10,10]'::int4range;
 
 int4range 
═══════════
 [-10,11)
 
SELECT '[0,)'::numrange;
 
 numrange 
══════════
 [0,)

    使用排除约束替代唯一约束
    
# 排除约束是使用GiST索引实现的，而GiST索引又是b树索引的扩展，我们需要安装 btree_gist插件支持我们想要操作符
postgres=# CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE EXTENSION
postgres=# truncate tab ;
TRUNCATE TABLE

#解释一下下面的排它约束，该约束表示不允许插入，a相等，b构建的整数范围相重叠的行，另外int4range(b, b, '[]')表示如果b不为NULL, int4range函数将构造一个只包含b的整数范围。如果b为NULL，则得到的范围在两端都是无界的。所以这正是我们测试所需要的
postgres=# ALTER TABLE tab ADD CONSTRAINT null_unique
EXCLUDE USING gist (
   a WITH =,
   int4range(b, b, '[]') WITH &&
);
ALTER TABLE
postgres=# INSERT INTO tab VALUES (1, NULL);
INSERT 0 1
postgres=# INSERT INTO tab VALUES (1, 2);
ERROR:  conflicting key value violates exclusion constraint "null_unique"
DETAIL:  Key (a, int4range(b, b, '[]'::text))=(1, [2,3)) conflicts with existing key (a, int4range(b, b, '[]'::text))=(1, (,)).
postgres=# INSERT INTO tab VALUES (5, 2);
INSERT 0 1
postgres=# INSERT INTO tab VALUES (5, 3);
INSERT 0 1
postgres=# INSERT INTO tab VALUES (5, NULL);
ERROR:  conflicting key value violates exclusion constraint "null_unique"
DETAIL:  Key (a, int4range(b, b, '[]'::text))=(5, (,)) conflicts with existing key (a, int4range(b, b, '[]'::text))=(5, [2,3)).

    由上可见：
    
  • 在已知的情况下，不要使用NULL值表示任何含义，这将使得我们数据的操作更加复杂
  • 如果使用B树索引的唯一约束不行，可以考虑使用GiST索引中通用的排它约束
  • 范围数据类型的灵活使用

    参考：
    
  • https://www.postgresql.org/docs/current/rangetypes.html
  • https://www.postgresql.org/docs/current/btree-gist.html
  • https://www.cybertec-postgresql.com/en/unique-constraint-null-conflicts-with-everything/
  • https://www.migops.com/blog/null-and-empty-string-in-oracle-vs-postgresql-vs-sql-server/

    总结 
    到此这篇关于Postgresql中null值和空字符串的文章就介绍到这了,更多相关Postgresql null值和空字符串内容请搜索电脑手机教程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持电脑手机教程网！
相关文章！