Oracle 和 PostgreSQL 在许多情况下的行为相似,但它们的一个不同之处在于,它们对 NULL 和空字符串的处理。
Oracle 中的 NULL 和空字符串
在 Oracle 中,NULL 和空字符串在数据库中存储的值是等效的。我们将通过一个简单的表和一些数据来演示这种行为:
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 的值、一个空字符串、一个包含单个空格的字符串,以及另一个包含 1 个字符的字符串。现在让我们来测试一下这些数据:
注意:请记住将默认的 null 输出从 '' 更改为 (null)
psql -P 'null=(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 | (null) | 1 | 0 | 0 |
| 2 | (null) | 1 | 0 | 0 |
| 3 | | 0 | 0 | 1 |
| 4 | x | 0 | 0 | 0 |
这告诉我们,空字符串在插入表中时被视为 NULL,并且不能将其与常规值进行比较,就好像它是空字符串一样,因为它是完整的 NULL。因此,空字符串不能存储在数据库中。
但是,如果我们有一个单独的空格,则不会转换它,因为它不是一个空字符串。当我们有任何非空白字符时,情况也是如此;都是一样的。
PostgreSQL 中的 NULL 和空字符串
但在 PostgreSQL 中,情况有所不同。让我们再做一次同样的事情,但这次是在 PostgreSQL 中。我们不需要对上述 DDL、DML 或 SQL 进行任何更改,因此让我们看看最终得到的结果:
| id | content | isnull | isempty | blank |
|----|---------|--------|---------|-------|
| 1 | (null) | 1 | 0 | 0 |
| 2 | | 0 | 1 | 0 |
| 3 | | 0 | 0 | 1 |
| 4 | x | 0 | 0 | 0 |
我们可以忽略底部的两行,因为正如预期的那样,功能是相同的。如果我们查看前两行,我们插入的 NULL 仍然被视为 NULL,不能与空字符串进行比较。但是当我们查看为第 2 行插入的空字符串时,我们没有 NULL 值,我们仍然有一个空字符串。没有发生任何转换,我们可以看到它在查询结果中不被视为 NULL,而是一个空字符串。PostgreSQL 在处理 NULL 值时的行为遵循了 SQL 标准。
NULL 和非 NULL
Oracle 和 PostgreSQL 之间的另一个重要区别,在 NULL 值与非 NULL 字符连接时。让我们看看,如果我们尝试将 NULL 或 1 个字符的字符串连接到示例表中的值,我们会得到什么。
我们将使用的查询是:
SELECT id, content,
content || NULL AS concatnull,
content || 'x' AS concatchar
FROM test;
Oracle:
| ID | CONTENT | CONCATNULL | CONCATCHAR |
|----|---------|------------|------------|
| 1 | (null) | (null) | x |
| 2 | (null) | (null) | x |
| 3 | | | x |
| 4 | x | x | xx |
PostgreSQL:
| id | content | concatnull | concatchar |
|----|---------|------------|------------|
| 1 | (null) | (null) | (null) |
| 2 | | (null) | x |
| 3 | | (null) | x |
| 4 | x | (null) | xx |
有趣的是,在 Oracle 中,将 NULL 和字符连接在一起的输出值还是该字符,而在 PostgreSQL 中,连接中的任一值存在 NULL,意味着输出结果总是为 NULL 值,无论它与什么连接。