CREATE TABLE test ( id NUMERIC ( 3, 0 ) PRIMARY KEY, content VARCHAR ( 255 ) ); INSERT INTO test (id, content) VALUES (1, NULL); INSERT INTO test (id, content) VALUES (2, ‘‘); INSERT INTO test (id, content) VALUES (3, ‘ ‘); INSERT INTO test (id, content) VALUES (4, ‘x‘);
这里我们显式插入了NULL和一个空字符串、一个只有单个空格的字符串。
SELECT id,content,
CASE WHEN content IS NULL THEN 1 ELSE 0 END AS isnull,
CASE WHEN content = ‘‘ THEN 1 ELSE 0 END AS isempty,
CASE WHEN content = ‘ ‘ THEN 1 ELSE 0 END AS blank
FROM
test;
ID CONTENT ISNULL ISEMPTY BLANK
---------- ------------------------------ ---------- ---------- ----------
1 1 0 0
2 1 0 0
3 0 0 1
4 x 0 0 0
SQL>
这里可以看出，插入数据库后，空字符串被当做NULL处理了。也就是说空字符串(empty string)在数据库中是不能存储的。
而只有单个空格的字符串是不会被转成NULL的。因为它并不是空字符串(empty string)
使用相同的表结构
postgres@=#SELECT id,content, postgres-# CASE WHEN content IS NULL THEN 1 ELSE 0 END AS isnull, postgres-# CASE WHEN content = ‘‘ THEN 1 ELSE 0 END AS isempty, postgres-# CASE WHEN content = ‘ ‘ THEN 1 ELSE 0 END AS blank postgres-# FROM postgres-# test; id | content | isnull | isempty | blank ----+---------+--------+---------+------- 1 | | 1 | 0 | 0 2 | | 0 | 1 | 0 3 | | 0 | 0 | 1 4 | x | 0 | 0 | 0 (4 rows)
插入的NULL仍然是NULL，不能和空字符串进行比较；插入的空字符串也没有被转化成NULL。
oracle中
SELECT id,content,
content || NULL AS concatnull,
content || ‘x‘ AS concatchar
FROM
test;
ID CONTENT CONCATNULL CONCATCHAR
---------- -------------------- -------------------- --------------------
1 x
2 x
3 x
4 x x xx
oracle将NULL和一个字符连接后，将字符作为结果返回。
postgresql中
postgres@=#SELECT id,content, postgres-# content || NULL AS concatnull, postgres-# content || ‘x‘ AS concatchar postgres-# FROM postgres-# test; id | content | concatnull | concatchar ----+---------+------------+------------ 1 | | | 2 | | | x 3 | | | x 4 | x | | xx
在pg中,NULLs和字符相连接后，NULL出现在任何一个值中都意味着结果是NULL作为输出值，而不管它连接的是什么。
oracle和postgresql中对待NULLs和空字符(empty string)的方式
原文：https://www.cnblogs.com/abclife/p/14741931.html