Postgresql中null值和空字符串舉例詳解

更新時間：2024年02月03日 08:32:45 作者：魂醉

在使用?PostgreSql時,實際場景中會出現(xiàn)某個字段為空或空字符串,下面這篇文章主要給大家介紹了關(guān)于Postgresql中null值和空字符串的相關(guān)資料,需要的朋友可以參考下

NULL和空字符串不同數(shù)據(jù)庫的表現(xiàn)

null和空字符串在不同的數(shù)據(jù)庫中表現(xiàn)不一樣，找了一張圖，可以很清晰的對比了解。

首先null不是一個空字符串，也不是一個為零的值，上圖，Oracle將NULL和空字符串都視為NULL。PostgreSQL將NULL視為NULL，將空字符串視為空字符串。與PostgreSQL類似，SQL Server也將NULL視為NULL，將空字符串視為空字符串。

Oracle需要一個字節(jié)來存儲NULL。然而，PostgreSQL和SQL Server都不需要任何空間來存儲NULL。

在Oracle中，存在唯一約束的列可以存儲任意數(shù)量的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也被認(rèn)為是等同于null的, unique字段不能存多個null值;
NULLS DISTINCT 保持向后兼容, unique字段可以存儲多個null值.

創(chuàng)建索引也支持該選項

CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;

看一下例子：

#創(chuàng)建測試表，插入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的組合是唯一的，并遵守以下兩個規(guī)則:

如果b為NULL，那么a不能有重復(fù)的值插入
如果b不為NULL,是其它的不同值，那么a可以插入重復(fù)的值

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

-- 可插入
INSERT INTO tab VALUES (1, NULL);
-- 由于規(guī)則1，不能插入，因為b為NULL
INSERT INTO tab VALUES (1, 2);
-- 可以插入
INSERT INTO tab VALUES (5, 2);
-- 可以插入，遵守規(guī)則2
INSERT INTO tab VALUES (5, 3);
-- 不能插入，違反規(guī)則1
INSERT INTO tab VALUES (5, NULL);

如果我們在a和b上建一個NULLS NOT DISTINCT的約束，可以實現(xiàn)上述要求嗎？

#創(chuàng)建唯一索引，插入數(shù)據(jù)測試，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實現(xiàn)目的

#Range Types幾個實例，[]表示閉區(qū)間，包括邊界，()表示開區(qū)間，不包括邊界
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索引實現(xiàn)的，而GiST索引又是b樹索引的擴(kuò)展，我們需要安裝 btree_gist插件支持我們想要操作符
postgres=# CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE EXTENSION
postgres=# truncate tab ;
TRUNCATE TABLE

#解釋一下下面的排它約束，該約束表示不允許插入，a相等，b構(gòu)建的整數(shù)范圍相重疊的行，另外int4range(b, b, '[]')表示如果b不為NULL, int4range函數(shù)將構(gòu)造一個只包含b的整數(shù)范圍。如果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值表示任何含義，這將使得我們數(shù)據(jù)的操作更加復(fù)雜
如果使用B樹索引的唯一約束不行，可以考慮使用GiST索引中通用的排它約束
范圍數(shù)據(jù)類型的靈活使用

參考：

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/