迁移 Oracle 到 PostgreSQL: 空字符串和 NULL

九月 30, 2024

摘要:本文讨论了 Oracle 和 PostgreSQL 处理 NULL 字符和空字符串的方式之间的差异。Oracle 将空字符串读取为 NULL,而 PostgreSQL 将其视为空字符串。将 NULL 值与非 NULL 字符连接,在 Oracle 中生成的结果还是该字符,但在 PostgreSQL 中会生成 NULL。

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 值,无论它与什么连接。