亚马逊AWS官方博客

从 Oracle 迁移到 PostgreSQL 时空字符串的处理

AWS Cloud 中的 Oracle 到 PostgreSQL 迁移可能是一个多阶段流程：从评估阶段直到移交阶段，涉及到不同的技术和技能。AWS 提供的服务可让 PostgreSQL 数据库部署以经济高效的方式轻松设置、管理和针对云进行扩展。这些服务包括 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 和Amazon Aurora PostgreSQL 兼容版。

在架构或代码转换阶段，我们可以使用 AWS Schema Conversion Tool (AWS SCT) 转换大部分代码。有关 AWS SCT 提出的代码转换异常，我们可以参考迁移模式和 Oracle 到 PostgreSQL 的迁移行动手册。

将代码从 Oracle 转换为兼容 PostgreSQL 的引擎可能涉及多个对您的整体迁移成功很重要的极端情况。在从 Oracle 迁移到 PostgreSQL 时，使用所有兼容 NULL 的运算符或表达式处理空字符串 (“) 对于实现转换代码的整体功能接受度并产生整体影响至关重要。

Oracle 数据库将空字符串和 NULL 视为相同。但是，在 PostgreSQL 中，空字符串和 NULL 是不同的。如果在 Oracle 上运行工作负载，则可以使用业务逻辑实现过程代码，假设在执行任何比较或 NULL 检查时将空字符串视为 NULL。在这篇博文中，我们将介绍在 PostgreSQL 中处理空字符串的不同变体以及迁移期间可行的解决方案。我们举例说明迁移的总体组成部分、影响和注意事项。

NULL 字符串与空字符串

Oracle 数据库将 NULL 字符串和空字符串视为相同；您可以在任何操作中互换使用空字符串和 NULL 字符串。Oracle 中的以下条件语句全部计算为 NULL：

SQL> set NULL (NULL);
 
SQL> SELECT length(NULL) res FROM DUAL;
 
       RES
	----------
	  (NULL)
 
 
SQL> SELECT length('') res FROM DUAL;
 
       RES
	----------
	  (NULL)

但是，在 PostgreSQL 中，NULL 表示值未知或未给出，空字符串是长度为 0 的字符串。因此，NULL 和空字符串的解释、表示和运算方式不同。PostgreSQL 数据库将空字符串视为可识别的值，其处理方式与其他可用字符类似：

postgres=> \pset null (NULL)

postgres=> SELECT length(NULL) res;

  res
--------
 (NULL)
(1 row)


postgres=> SELECT length('') res;

 res
-----
   0
(1 row)

下表总结了这些不同。

表达式 Oracle 结果 PostgreSQL 结果
NULL = NULL NULL NULL
” = NULL NULL NULL
” = ” NULL：空字符串被视为 NULL，因此不能与任何内容进行比较，甚至不能与其自身进行比较 TRUE：空字符串具有可比性，因为它们是 PostgreSQL 中长度为 0 的字符串

请注意，PostgreSQL 中 NULL 字符串和空字符串的区别如下：

  • NULL 可以分配给任何类型，而空字符串则不同，空字符串与日期或数值字段不兼容。请参见以下示例代码：
postgres=> select null::int, null::date;
 int4 | date
------+------
 |
(1 row)

postgres=> select ''::int;
ERROR: invalid input syntax for type integer: ""
LINE 1: select ''::int;
^

postgres=> select ''::date;
ERROR: invalid input syntax for type date: ""
LINE 1: select ''::date;
^
  • NULL 是一个未知值。它没有值，而空字符串则不同，空字符串是一个值，但却是一个空值。
  • 查找 NULL 的大小或长度会生成 NULL 结果。空字符串是长度为 0 的字符串。
  • NULL = NULL 的计算结果为 NULL，而 ”=” 则不同，其计算结果为 TRUE。
  • NULL 不能与任何内容进行比较，甚至不能与其自身进行比较，而空字符串则可以比较。

Oracle 与 PostgreSQL 最大的一个区别是 NULL 和空字符串在复合唯一索引和约束方面的操作方式。PostgreSQL 允许在唯一索引中包含多行 NULL。相反，Oracle 在复合唯一约束或唯一索引中限制多个 NULL 实例和空字符串。当遇到空字符串时，Oracle 和 PostgreSQL 都会为多个条目引发错误。

以下是使用 Oracle 的示例：

SQL> CREATE TABLE test_unq(c1 integer, c2 varchar2(10));

SQL> ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);

SQL> insert into test_unq values(1, null);

1 row created.

SQL> insert into test_unq values(1, null);
insert into test_unq values(1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (TESTORA.IDX_TEST_UNQ) violated

由于 Oracle 将空字符串视为 NULL，因此违反了唯一约束或唯一索引多个空字符串条目的约定：

SQL> insert into test_unq values(2, '');

1 row created.

SQL> insert into test_unq values(2, '');
insert into test_unq values(2, '')
*
ERROR at line 1:
ORA-00001: unique constraint (TESTORA.TEST_UNQ_C) violated

以下是使用 PostgreSQL 的相同示例：

postgres=> CREATE TABLE test_unq(c1 integer, c2 character varying(10));

postgres=> ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);

postgres=> insert into test_unq values(1, null);
INSERT 0 1

postgres=> insert into test_unq values(1, null);
INSERT 0 1

PostgreSQL 数据库将空字符串和 NULL 视为不同。所有空字符串都是相等的，因此违反了唯一约束或唯一索引多个空字符串条目的约定：

postgres=> insert into test_unq values(2, '');
INSERT 0 1

postgres=> insert into test_unq values(2, '');
ERROR:  duplicate key value violates unique constraint "test_unq_c"
DETAIL:  Key (c1, c2)=(2, ) already exists.

在 PostgreSQL 中处理空字符串

从前面的讨论中可以明显看出，在从 Oracle 迁移到 PostgreSQL 时，空字符串的处理非常重要且至关重要。

让我们创建一个示例表并向其插入数据。您可以使用以下 SQL 语句在 Oracle 和 PostgreSQL 中创建表并插入数据：

CREATE TABLE Test_Empty_or_Null(
  tid    INTEGER,
  tname  VARCHAR(10),
  txdate DATE
);

-- insert statement #1
INSERT INTO Test_Empty_or_Null VALUES(1, 'Test1', current_date);

-- insert statement #2
INSERT INTO Test_Empty_or_Null VALUES(2, NULL, current_date);

-- insert statement #3
INSERT INTO Test_Empty_or_Null VALUES(3, '', current_date);

COMMIT;

在 Oracle 中，由于空字符串被视为 NULL，因此前面的插入语句 #2 和 #3 将为表中的列 tname 存储 NULL。但是，在 PostgreSQL 中，插入语句 #2 将为表存储 NULL，插入语句 #3 将为表存储空字符串。

如果使用 IS NULL 或 IS NOT NULL 运算符选择行，则可以看出 Oracle 和 PostgreSQL 的区别。

以下是 Oracle 代码：

SQL> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;

     TID    TNAME      TXDATE
---------- ---------- ---------
      2 		  	  20-AUG-21
      3 		 	  20-AUG-21

以下是 PostgreSQL 代码：

postgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;
   tid 	| tname | txdate
   -----+-------+------------
    2 	| 	    | 2021-08-20
(1 row)

如果要在 PostgreSQL 中选择所有包含 NULL 或空字符串的行，则必须在查询的 WHERE 子句中显式添加空字符串条件：

 postgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL OR tname = '';
   tid 	| tname | txdate
   -----+-------+------------
    2 	| 	    | 2021-08-20
    3 	| 	    | 2021-08-20
(2 rows)

或者，您可以在 PostgreSQL 中使用 COALESCE 或 NULLIF 等空处理函数来处理包含空字符串的情况。

COALESCE 函数

COALESCE 函数返回其第一个非 NULL 参数。仅当所有参数均为 NULL 时才返回 NULL。您可以将 WHERE tname IS NULL 筛选条件调整为 WHERE coalesce(tname, '') = '' 以获取 tname 为空字符串或未知的所有行。

但是，更改 SQL 查询中的 WHERE 子句以支持该功能可能会影响查询的运行计划。数据库可能不会优化计划，因为特定的筛选器已更改，并且不会使用在此列上创建的任何索引。您的查询可能会变慢。在这些情况下，您必须分析查询运行计划，重新访问创建的索引，并根据需要在表上创建部分索引以优化查询。查询优化不在这篇博文的讨论范围内。

以下代码是 COALESCE 函数的示例：

PostgreSQL

postgres=> SELECT * FROM Test_Empty_or_Null WHERE coalesce(tname, '') = '';

	    tid	| tname |   txdate
	    ----+-------+------------
         2 	| 	    |  2021-08-20
         3 	| 	    |  2021-08-20
        (2 rows)

Oracle：

SQL> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;

             TID      TNAME      TXDATE
          ---------- ---------- ---------
             2 		             20-AUG-21
             3 		             20-AUG-21

使用 COALESCE 处理空字符串的 IS NULL 或 IS NOT NULL 需要进行大量代码更改。更简单的方法是使用 NULLIF 函数。

NULLIF 函数

如果两个参数相同，则 NULLIF 将返回 NULL。当两个参数不同时，它返回第 1 个参数。我们用它来比较输入表达式和空字符串并返回 NULL：

postgres=> \pset null (NULL)

postgres=> SELECT NULLIF('', '') res;
  res
--------
 (NULL)
(1 row)

对于可能遇到空字符串 (”) 作为输入的情况，我们会使用 NULLIF 转换原生 PostgreSQL 函数。例如，如果您有一个像 “tname IS NULL” 这样的条件，其中 “tname” 可以包含 NULL，那么您可以在 PostgreSQL 中更改 “nullif(tname, '') IS NULL”。

postgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;
	   tid 	| tname | txdate
	   -----+-------+------------
        2 	| 	    | 2021-08-20
 (1 row)

/* NULLIF 作为表达式的一部分是必需的，因为 PostgreSQL “IS NULL” 运算符不处理空字符串 */

postgres=> SELECT * FROM Test_Empty_or_Null WHERE nullif(tname, '') IS NULL;
	   tid 	| tname | txdate
	   -----+-------+------------
		2 	| 	    | 2021-08-20
		3 	| 	    | 2021-08-20
(2 rows)

对于所有此类 NULL 和空字符串的情况，让我们深入研究其他需要适当注意以将 Oracle 过程代码移植到 PostgreSQL 的数据库表达式或函数。

NULL 处理函数和空字符串的挑战

Oracle 和 PostgreSQL 中的 NULL 处理函数在任何比较或表达式中的工作方式都是相同的。但是，由于空字符串是 Oracle 中表示 NULL 的另一种方式，因此使用 NULL 或空字符串操作 NULL 处理函数时没有观察到差异。但是在 PostgreSQL 数据库中，NULL 处理函数与空字符串结合使用的计算结果为 FALSE。这在从 Oracle 迁移到 PostgreSQL 以模拟 NULL 处理函数在处理空字符串时的相同行为时产生了挑战。

下表总结了 Oracle 和 PostgreSQL 关于空字符串的整体行为。

输入表达式 Oracle 条件评估 PostgreSQL 条件评估
” IS NULL True False
” IS NOT NULL False True
NVL(”, ‘A’) A 在 PostgreSQL 中不可用。您可以使用 Orafce 扩展 NVL 函数。必须将 NULLIF 与 NVL 函数结合使用，才能将空字符串解析为 NULL。
COALESCE(”, ‘A’) A ” (empty string)
DECODE(”, NULL, ‘A’, ‘B’) A 在 PostgreSQL 中不可用。您可以使用 Orafce 扩展 DECODE 函数。必须将 NULLIF 与 DECODE 函数结合使用，才能将空字符串解析为 NULL。另一种方法是将 DECODE 转换为 CASE。
CASE WHEN ” IS NULL True False
CASE ” WHEN NULL False False

现在，我们已经更好地了解了 Oracle 和 PostgreSQL 的差异，接下来我们可以逐步了解一些在转换阶段使用的变通方法和最佳实践。

处理 IS NULL 和 IS NOT NULL

如果您在 Oracle 中多次使用诸如 IS NULL 和 IS NOT NULL 之类的条件表达式作为过程代码的一部分，则必须验证它们在 PostgreSQL 中是否可以处理空字符串。如果在表达式求值期间列或变量值可能变成空字符串，请在 PostgreSQL 中使用 NULLIF 函数。

在从 Oracle 到 PostgreSQL 数据库的代码迁移期间，只有字符变化的数据类型才需要添加 NULLIF。这与 NUMERIC 或 DATE 等其他数据类型不兼容。

处理 NVL 和 COALESCE

PostgreSQL 数据库没有内置的 NVL 函数，而是支持 COALESCE 函数，后者是符合 ANSII 的 NVL 函数。COALESCE 函数非常适合字符串以外的数据类型。这是因为字符串可以接受空字符串。对于可能接受空字符串作为输入的情况，必须使用 PostgreSQL COALESCE 函数小心处理它们。

以下是 Oracle 代码：

SQL> SELECT coalesce(NULL, 'A') res FROM DUAL;

  RES
----------
   A

以下是 PostgreSQL 代码：

postgres=> SELECT coalesce(NULL, 'A') res;
   res
  -----
   A
(1 row)

以下示例显示了将空字符串作为输入时 COALESCE 的功能有何不同。

以下是 Oracle 代码：

SQL> SELECT coalesce('', 'A') res FROM DUAL;

   RES
----------
    A

以下是 PostgreSQL 代码：

postgres=> SELECT coalesce('', 'A') res;
  res
 -----

 (1 row)

尽管 COALESCE 在 PostgreSQL 中的行为符合标准，但在从 Oracle 迁移时，它并不总是基于输入值或变量值兼容。让我们了解一下 COALESCE 处理空字符串的解决方法。以下是 PostgreSQL 代码：

postgres=> SELECT coalesce(nullif('', ''), 'a') res;
  res
 -----
   a
(1 row)

Oracle NVL 函数

Orafce 是 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 和 Amazon Aurora PostgreSQL 兼容版中支持的扩展。当您在 PostgreSQL 中创建此扩展时，它会带来包含一些有用函数的模块，这些函数可以帮助您将 Oracle 应用程序移植到 PostgreSQL。Orafce 提供了 NVL 函数，您可以使用该函数从 Oracle 迁移代码，而无需更改代码。

以下示例显示了 Orafce NVL 如何处理 NULL 输入，但不像 Oracle 数据库那样将空字符串视为 NULL：

postgres=> SELECT nvl(null, 'A'::text) res;
 res
-----
 A
(1 row)

postgres=> SELECT nvl('', 'A'::text) res;
 res
-----

(1 row)

因此，如果您在代码迁移或开发过程中使用 Orafce NVL 函数，则必须确保处理字符变化或文本数据类型的空字符串，如下例所示：

postgres=> SELECT nvl(nullif('', ''), 'A'::text) res;
 res
-----
 A
(1 row)

模拟 NVL 函数作为字符变化或文本参数的包装器

在 COALESCE 或 Orafce NVL 中为每个字符变化或文本数据输入添加 NULLIF 函数可能会使代码迁移过程繁琐且耗时。为了降低这种风险，一种变通方法是在 PostgreSQL 中创建一个包装器函数，该函数将空字符串解析为 NULL，同时提高性能。

下面显示了 NVL 函数的包装器代码，其中包含两个文本类型的参数：

CREATE OR REPLACE FUNCTION nvl(text, text)
RETURNS text
LANGUAGE sql
AS $$
    select coalesce(NULLIF($1,''), NULLIF($2,''))
$$;

让我们测试一下我们创建的 NVL 包装器函数，看看它是否可以处理 NULL 以及空字符串：

postgres=> SELECT coalesce('', 'A') res;
  res
 -----

(1 row)


postgres=> SELECT nvl(NULL, 'A') res;
  res
 -----
   A
(1 row)


postgres=> SELECT nvl('', 'A') res;
  res
 -----
   A
(1 row)

由于空字符串可能是 Oracle 中字符或文本数据类型的输入参数，因此您可以创建前面的包装器函数来模拟 Oracle NVL 函数的类似行为并加快代码迁移过程。

由于 PostgreSQL 中的空字符串行为，我们创建的 NVL 包装器函数仅适用于字符变化或文本数据类型。对于其他数据类型，您可以选择 COALESCE 或 Orafce NVL 函数。

处理 DECODE 和 CASE 表达式

像 CASE 和 DECODE 这样的表达式可以是许多动态查询或条件表达式的一部分，您需要确保它对于 NULL 和 “as input 保持类似的行为。请参阅以下 Oracle 代码：

SET serveroutput on;

DECLARE
    var  varchar2(100);
    res  varchar2(100);

BEGIN
--CASE Expression - Input as NULL
    var := NULL;
    SELECT CASE WHEN var IS NULL THEN 'NULL' 
                ELSE 'NOT NULL'
           END 
      INTO res
      FROM dual;
dbms_output.put_line('CASE Expression - Input as NULL, Result is '|| res);

--CASE Expression - Input as Empty String
    var := '';
    SELECT CASE WHEN var IS NULL THEN 'NULL' 
                ELSE 'NOT NULL'
           END 
      INTO res
      FROM dual;
dbms_output.put_line('CASE Expression - Input as empty string, Result is '|| res);

--DECODE Expression - Input as NULL
    var := NULL;
    SELECT DECODE(var, null, 'NULL', 'NOT NULL') 
      INTO res 
      FROM dual;
dbms_output.put_line('DECODE Expression - Input as NULL, Result is '|| res);

--DECODE Expression - Input as Empty String
    var := '';
    SELECT DECODE(var, null, 'NULL', 'NOT NULL') 
      INTO res 
      FROM dual;
dbms_output.put_line('DECODE Expression - Input as empty string, Result is '|| res);

END;
/

您将获得以下输出：

CASE Expression - Input as NULL, Result is NULL
CASE Expression - Input as empty string, Result is NULL
DECODE Expression - Input as NULL, Result is NULL
DECODE Expression - Input as empty string, Result is NULL

PostgreSQL 引擎提供了额外的函数和扩展来缓解空字符串条件求值，并提供与 Oracle 引擎相同的行为：

SET client_min_messages = debug;

DO $$
DECLARE
    var  varchar(100);
    res  varchar(100);

BEGIN
--CASE Expression - Input as NULL
    var := NULL;
    SELECT CASE WHEN var IS NULL THEN 'NULL' 
                ELSE 'NOT NULL'
           END 
      INTO res;
Raise debug using message := concat_ws('', 'CASE Expression - Input as NULL, Result is ', res);

--CASE Expression - Input as Empty String
    var := '';
    SELECT CASE WHEN var IS NULL THEN 'NULL' 
                ELSE 'NOT NULL'
           END 
      INTO res;
Raise debug using message := concat_ws('', 'CASE Expression - Input as empty string, Result is ', res);

--DECODE Expression – No DECODE function in PostgreSQL

END $$;

您将获得以下输出：

DEBUG:  CASE Expression - Input as NULL, Result is NULL
DEBUG:  CASE Expression - Input as empty string, Result is NOT NULL

PostgreSQL 中不存在像 DECODE 这样的函数。您需要手动转换为 CASE 表达式。您也可以使用 Orafce DECODE 函数。

在 Oracle 中，DECODE 和 CASE 表达式都将 NULL 字符串和空字符串视为相同，在将它们转换为 PostgreSQL 时需要正确处理。

有了这样的理解，让我们来探索在不破坏功能的情况下将 CASE 或 DECODE 迁移到 PostgreSQL 的各种选项和最佳实践。

选项 1：将 DECODE 转换为 CASE 表达式

从 Oracle 迁移代码时，您可以在 PostgreSQL 中将所有 DECODE 函数转换为传统的 CASE 表达式。但是当空字符串可能作为 CASE 表达式的输入出现时，您必须始终记住 CASE 的正确实现。

以下是 Oracle 代码：

SELECT DECODE(var, null, 'NULL', 'NOT NULL') 
    FROM DUAL;

以下是在 PostgreSQL 中编写搜索 CASE 表达式的正确方法：

SELECT CASE WHEN nullif(var, '') IS null THEN 'NULL' 
            ELSE 'NOT NULL'
       END;

选项 2：在 PostgreSQL 中使用 Orafce DECODE 函数

让我们使用不同类型的输入参数来测试 Orafce 扩展提供的 DECODE 函数，并学习如何正确处理空字符串。

当输入为非 NULL 字符串时，Oracle DECODE 和 Orafce DECODE 提供相同的输出。

以下是 Oracle 代码：

SQL> SELECT DECODE('one', null, 'NULL', 'NOT NULL') res FROM DUAL;

  RES
--------
NOT NULL

以下是 PostgreSQL 代码：

postgres=> SELECT DECODE('one'::text, null, 'NULL', 'NOT NULL') res;
   res
----------
 NOT NULL
(1 row)

当输入为 NULL 时，Oracle DECODE 和 Orafce DECODE 提供相同的输出。

以下是 Oracle 代码：

SQL> SELECT DECODE(null, null, 'NULL', 'NOT NULL') res FROM DUAL;
         RES
         ----
         NULL

以下是 PostgreSQL 代码：

postgres=> SELECT DECODE(null::text, null, 'NULL', 'NOT NULL') res;
   res
----------
   NULL
 (1 row)

当输入为空字符串时，Oracle DECODE 和 Orafce DECODE 提供不同的输出。

以下是 Oracle 代码：

SQL> SELECT DECODE('', null, 'NULL', 'NOT NULL') res FROM DUAL;
          RES
          ----
          NULL

以下是 PostgreSQL 代码：

postgres=> SELECT DECODE(''::text, null, 'NULL', 'NOT NULL') res;
   res
----------
NOT NULL
(1 row)

在这种情况下，您必须转换为 CASE（如前所述），或者将 NULLIF 与 DECODE 结合使用：

postgres=> SELECT DECODE(nullif('', ''), null, 'NULL', 'NOT NULL') res;
   res
----------
  NULL
 (1 row)

结论

在将数据库从 Oracle 迁移到 PostgreSQL 时，处理空字符串并使用不同的运算符、表达式或函数（如 IS NULL、NVL、CASE 和 DECODE）进行求值应该是一个重要的考虑因素。在这篇博文中，我们讨论了从 Oracle 迁移到 PostgreSQL 期间处理空字符串重要性和可行的解决方案。我们的示例涵盖了处理空字符串的不同变体，以了解迁移到 PostgreSQL 的总体组成部分、影响和注意事项。

如果您对这篇博文有任何疑问或建议，请留言。

关于作者

Sashikanta Pattanayak 是 AWS 专业服务团队的首席顾问。他与客户合作，在 AWS Cloud 中构建可扩展、高度可用且安全的解决方案。他擅长同构和异构数据库迁移。

 

 

 

Deepak Mahto 曾是印度 AWS Proserve 团队的顾问。他曾担任数据库迁移主管，帮助并支持客户从商业引擎迁移到 Amazon RDS。他热衷于自动化，并设计和实施了多个与数据库或迁移相关的工具。

 

 

 

Vinay Paladi 是 Amazon Web Services 专业服务团队的数据库顾问。他是一名数据库迁移专家，帮助并支持客户构建高可用性、经济高效的数据库解决方案，并将其商业引擎迁移到 AWS Cloud。他热衷于构建创新的解决方案，以加快数据库向云的迁移过程。