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

{"value":"Amazon Cloud 中的 Oracle 到 PostgreSQL 迁移可能是一个多阶段流程：从评估阶段直到移交阶段，涉及到不同的技术和技能。Amazon 提供的服务可让 PostgreSQL 数据库部署以经济高效的方式轻松设置、管理和针对云进行扩展。这些服务包括[ Amazon Relational Database Service (Amazon RDS) for PostgreSQL](https://aws.amazon.com/rds/postgresql/) 和 [Amazon Aurora PostgreSQL 兼容版](https://aws.amazon.com/rds/aurora/details/postgresql-details/)。\n\n在架构或代码转换阶段，我们可以使用 [Amazon Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) (Amazon SCT) 转换大部分代码。有关 Amazon SCT 提出的代码转换异常，我们可以参考[迁移模式](https://aws.amazon.com/prescriptive-guidance/?apg-all-cards.sort-by=item.additionalFields.sortText&apg-all-cards.sort-order=desc&awsf.apg-new-filter=*all&awsf.apg-content-type-filter=*all&awsf.apg-code-filter=*all&awsf.apg-category-filter=*all&awsf.apg-rtype-filter=*all&awsf.apg-isv-filter=*all&awsf.apg-product-filter=*all&awsf.apg-env-filter=*all)和 [Oracle 到 PostgreSQL 的迁移行动手册](https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook-12.4.pdf)。\n\n将代码从 Oracle 转换为兼容 PostgreSQL 的引擎可能涉及多个对您的整体迁移成功很重要的极端情况。在从 Oracle 迁移到 PostgreSQL 时，使用所有兼容 NULL 的运算符或表达式处理空字符串 (“) 对于实现转换代码的整体功能接受度并产生整体影响至关重要。\n\nOracle 数据库将空字符串和 NULL 视为相同。但是，在 PostgreSQL 中，空字符串和 NULL 是不同的。如果在 Oracle 上运行工作负载，则可以使用业务逻辑实现过程代码，假设在执行任何比较或 NULL 检查时将空字符串视为 NULL。在这篇博文中，我们将介绍在 PostgreSQL 中处理空字符串的不同变体以及迁移期间可行的解决方案。我们举例说明迁移的总体组成部分、影响和注意事项。\n\n#### **NULL 字符串与空字符串**\n\nOracle 数据库将 NULL 字符串和空字符串视为相同；您可以在任何操作中互换使用空字符串和 NULL 字符串。Oracle 中的以下条件语句全部计算为 NULL：\n\n```\nSQL> set NULL (NULL);\n \nSQL> SELECT length(NULL) res FROM DUAL;\n \n RES\n\t----------\n\t (NULL)\n \n \nSQL> SELECT length('') res FROM DUAL;\n \n RES\n\t----------\n\t (NULL)\n```\n\n但是，在 PostgreSQL 中，NULL 表示值未知或未给出，空字符串是长度为 0 的字符串。因此，NULL 和空字符串的解释、表示和运算方式不同。PostgreSQL 数据库将空字符串视为可识别的值，其处理方式与其他可用字符类似：\n\n```\npostgres=> \\pset null (NULL)\n\npostgres=> SELECT length(NULL) res;\n\n res\n--------\n (NULL)\n(1 row)\n\n\npostgres=> SELECT length('') res;\n\n res\n-----\n 0\n(1 row)\n```\n\n下表总结了这些不同。\n\n![image.png](https://dev-media.amazoncloud.cn/ee8ee11ad9f04032ac2698fa9028d3ff_image.png)\n\n请注意，PostgreSQL 中 NULL 字符串和空字符串的区别如下：\n\n- NULL 可以分配给任何类型，而空字符串则不同，空字符串与日期或数值字段不兼容。请参见以下示例代码：\n\n```\npostgres=> select null::int, null::date;\n int4 | date\n------+------\n |\n(1 row)\n\npostgres=> select ''::int;\nERROR: invalid input syntax for type integer: \"\"\nLINE 1: select ''::int;\n^\n\npostgres=> select ''::date;\nERROR: invalid input syntax for type date: \"\"\nLINE 1: select ''::date;\n^\n```\n\n- NULL 是一个未知值。它没有值，而空字符串则不同，空字符串是一个值，但却是一个空值。\n- 查找 NULL 的大小或长度会生成 NULL 结果。空字符串是长度为 0 的字符串。\n- NULL = NULL 的计算结果为 NULL，而 ”=” 则不同，其计算结果为 TRUE。\n- NULL 不能与任何内容进行比较，甚至不能与其自身进行比较，而空字符串则可以比较。\n\nOracle 与 PostgreSQL 最大的一个区别是 NULL 和空字符串在复合唯一索引和约束方面的操作方式。PostgreSQL 允许在唯一索引中包含多行 NULL。相反，Oracle 在复合唯一约束或唯一索引中限制多个 NULL 实例和空字符串。当遇到空字符串时，Oracle 和 PostgreSQL 都会为多个条目引发错误。\n\n以下是使用 Oracle 的示例：\n\n```\nSQL> CREATE TABLE test_unq(c1 integer, c2 varchar2(10));\n\nSQL> ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);\n\nSQL> insert into test_unq values(1, null);\n\n1 row created.\n\nSQL> insert into test_unq values(1, null);\ninsert into test_unq values(1, null)\n*\nERROR at line 1:\nORA-00001: unique constraint (TESTORA.IDX_TEST_UNQ) violated\n```\n\n由于 Oracle 将空字符串视为 NULL，因此违反了唯一约束或唯一索引多个空字符串条目的约定：\n\n```\nSQL> insert into test_unq values(2, '');\n\n1 row created.\n\nSQL> insert into test_unq values(2, '');\ninsert into test_unq values(2, '')\n*\nERROR at line 1:\nORA-00001: unique constraint (TESTORA.TEST_UNQ_C) violated\n\n```\n\n以下是使用 PostgreSQL 的相同示例：\n\n```\npostgres=> CREATE TABLE test_unq(c1 integer, c2 character varying(10));\n\npostgres=> ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);\n\npostgres=> insert into test_unq values(1, null);\nINSERT 0 1\n\npostgres=> insert into test_unq values(1, null);\nINSERT 0 1\n```\n\nPostgreSQL 数据库将空字符串和 NULL 视为不同。所有空字符串都是相等的，因此违反了唯一约束或唯一索引多个空字符串条目的约定：\n\n```\npostgres=> insert into test_unq values(2, '');\nINSERT 0 1\n\npostgres=> insert into test_unq values(2, '');\nERROR: duplicate key value violates unique constraint \"test_unq_c\"\nDETAIL: Key (c1, c2)=(2, ) already exists.\n```\n\n#### **在 PostgreSQL 中处理空字符串**\n\n从前面的讨论中可以明显看出，在从 Oracle 迁移到 PostgreSQL 时，空字符串的处理非常重要且至关重要。\n\n让我们创建一个示例表并向其插入数据。您可以使用以下 SQL 语句在 Oracle 和 PostgreSQL 中创建表并插入数据：\n\n```\nCREATE TABLE Test_Empty_or_Null(\n tid INTEGER,\n tname VARCHAR(10),\n txdate DATE\n);\n\n-- insert statement #1\nINSERT INTO Test_Empty_or_Null VALUES(1, 'Test1', current_date);\n\n-- insert statement #2\nINSERT INTO Test_Empty_or_Null VALUES(2, NULL, current_date);\n\n-- insert statement #3\nINSERT INTO Test_Empty_or_Null VALUES(3, '', current_date);\n\nCOMMIT;\n\n```\n\n在 Oracle 中，由于空字符串被视为 NULL，因此前面的插入语句 #2 和 #3 将为表中的列 tname 存储 NULL。但是，在 PostgreSQL 中，插入语句 #2 将为表存储 NULL，插入语句 #3 将为表存储空字符串。\n\n如果使用 IS NULL 或 IS NOT NULL 运算符选择行，则可以看出 Oracle 和 PostgreSQL 的区别。\n\n以下是 Oracle 代码：\n\n```\nSQL> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n\n TID TNAME TXDATE\n---------- ---------- ---------\n 2 \t\t \t 20-AUG-21\n 3 \t\t \t 20-AUG-21\n\n```\n\n以下是 PostgreSQL 代码：\n\n```\npostgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n tid \t| tname | txdate\n -----+-------+------------\n 2 \t| \t | 2021-08-20\n(1 row)\n```\n\n如果要在 PostgreSQL 中选择所有包含 NULL 或空字符串的行，则必须在查询的 WHERE 子句中显式添加空字符串条件：\n\n```\npostgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL OR tname = '';\n tid \t| tname | txdate\n -----+-------+------------\n 2 \t| \t | 2021-08-20\n 3 \t| \t | 2021-08-20\n(2 rows) \n```\n\n或者，您可以在 PostgreSQL 中使用 COALESCE 或 NULLIF 等空处理函数来处理包含空字符串的情况。\n\n#### **COALESCE 函数**\n\nCOALESCE 函数返回其第一个非 NULL 参数。仅当所有参数均为 NULL 时才返回 NULL。您可以将 ```WHERE tname IS NULL``` 筛选条件调整为``` WHERE coalesce(tname, '') = ''``` 以获取 ```tname``` 为空字符串或未知的所有行。\n\n但是，更改 SQL 查询中的 WHERE 子句以支持该功能可能会影响查询的运行计划。数据库可能不会优化计划，因为特定的筛选器已更改，并且不会使用在此列上创建的任何索引。您的查询可能会变慢。在这些情况下，您必须分析查询运行计划，重新访问创建的索引，并根据需要在表上创建[部分索引](https://www.postgresql.org/docs/14/indexes-partial.html)以优化查询。查询优化不在这篇博文的讨论范围内。\n\n以下代码是 COALESCE 函数的示例：\n\nPostgreSQL\n\n```\npostgres=> SELECT * FROM Test_Empty_or_Null WHERE coalesce(tname, '') = '';\n\n\t tid\t| tname | txdate\n\t ----+-------+------------\n 2 \t| \t | 2021-08-20\n 3 \t| \t | 2021-08-20\n (2 rows)\n```\n\nOracle：\n\n```\nSQL> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n\n TID TNAME TXDATE\n ---------- ---------- ---------\n 2 \t\t 20-AUG-21\n 3 \t\t 20-AUG-21\n```\n\n使用 COALESCE 处理空字符串的 IS NULL 或 IS NOT NULL 需要进行大量代码更改。更简单的方法是使用 NULLIF 函数。\n\n#### **NULLIF 函数**\n\n如果两个参数相同，则 [NULLIF](https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-NULLIF) 将返回 NULL。当两个参数不同时，它返回第 1 个参数。我们用它来比较输入表达式和空字符串并返回 NULL：\n\n```\npostgres=> \\pset null (NULL)\n\npostgres=> SELECT NULLIF('', '') res;\n res\n--------\n (NULL)\n(1 row)\n```\n\n对于可能遇到空字符串 (”) 作为输入的情况，我们会使用 NULLIF 转换原生 PostgreSQL 函数。例如，如果您有一个像 ```“tname IS NULL” ```这样的条件，其中 ```“tname”``` 可以包含 NULL，那么您可以在 PostgreSQL 中更改 ```“nullif(tname, '') IS NULL”```。\n\n```\npostgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n\t tid \t| tname | txdate\n\t -----+-------+------------\n 2 \t| \t | 2021-08-20\n (1 row)\n\n/* NULLIF 作为表达式的一部分是必需的，因为 PostgreSQL “IS NULL” 运算符不处理空字符串 */\n\npostgres=> SELECT * FROM Test_Empty_or_Null WHERE nullif(tname, '') IS NULL;\n\t tid \t| tname | txdate\n\t -----+-------+------------\n\t\t2 \t| \t | 2021-08-20\n\t\t3 \t| \t | 2021-08-20\n(2 rows) \n```\n\n对于所有此类 NULL 和空字符串的情况，让我们深入研究其他需要适当注意以将 Oracle 过程代码移植到 PostgreSQL 的数据库表达式或函数。\n\n#### **NULL 处理函数和空字符串的挑战**\n\n\nOracle 和 PostgreSQL 中的 NULL 处理函数在任何比较或表达式中的工作方式都是相同的。但是，由于空字符串是 Oracle 中表示 NULL 的另一种方式，因此使用 NULL 或空字符串操作 NULL 处理函数时没有观察到差异。但是在 PostgreSQL 数据库中，NULL 处理函数与空字符串结合使用的计算结果为 FALSE。这在从 Oracle 迁移到 PostgreSQL 以模拟 NULL 处理函数在处理空字符串时的相同行为时产生了挑战。\n\n下表总结了 Oracle 和 PostgreSQL 关于空字符串的整体行为。\n\n![image.png](https://dev-media.amazoncloud.cn/cec4b359447a4970b12a750f3d8d0c08_image.png)\n\n现在，我们已经更好地了解了 Oracle 和 PostgreSQL 的差异，接下来我们可以逐步了解一些在转换阶段使用的变通方法和最佳实践。\n\n#### **处理 IS NULL 和 IS NOT NULL**\n\n如果您在 Oracle 中多次使用诸如 IS NULL 和 IS NOT NULL 之类的条件表达式作为过程代码的一部分，则必须验证它们在 PostgreSQL 中是否可以处理空字符串。如果在表达式求值期间列或变量值可能变成空字符串，请在 PostgreSQL 中使用 NULLIF 函数。\n\n在从 Oracle 到 PostgreSQL 数据库的代码迁移期间，只有字符变化的数据类型才需要添加 NULLIF。这与 NUMERIC 或 DATE 等其他数据类型不兼容。\n\n#### **处理 NVL 和 COALESCE**\n\nPostgreSQL 数据库没有内置的 NVL 函数，而是支持 COALESCE 函数，后者是符合 ANSII 的 NVL 函数。COALESCE 函数非常适合字符串以外的数据类型。这是因为字符串可以接受空字符串。对于可能接受空字符串作为输入的情况，必须使用 PostgreSQL COALESCE 函数小心处理它们。\n\n以下是 Oracle 代码：\n\n```\nSQL> SELECT coalesce(NULL, 'A') res FROM DUAL;\n\n RES\n----------\n A\n```\n\n以下是 PostgreSQL 代码：\n\n```\npostgres=> SELECT coalesce(NULL, 'A') res;\n res\n -----\n A\n(1 row)\n```\n \n以下示例显示了将空字符串作为输入时 COALESCE 的功能有何不同。\n\n以下是 Oracle 代码：\n\n```\nSQL> SELECT coalesce('', 'A') res FROM DUAL;\n\n RES\n----------\n A\n```\n\n以下是 PostgreSQL 代码：\n\n```\npostgres=> SELECT coalesce('', 'A') res;\n res\n -----\n\n (1 row)\n```\n\n尽管 COALESCE 在 PostgreSQL 中的行为符合标准，但在从 Oracle 迁移时，它并不总是基于输入值或变量值兼容。让我们了解一下 COALESCE 处理空字符串的解决方法。以下是 PostgreSQL 代码：\n\n```\npostgres=> SELECT coalesce(nullif('', ''), 'a') res;\n res\n -----\n a\n(1 row)\n```\n\n#### **Oracle NVL 函数**\n\n[Orafce](https://github.com/orafce/orafce) 是 [Amazon Relational Database Service (Amazon RDS) for PostgreSQL](https://aws.amazon.com/rds/postgresql/) 和 [Amazon Aurora PostgreSQL](https://aws.amazon.com/rds/aurora/postgresql-features/) 兼容版中支持的扩展。当您在 PostgreSQL 中创建此扩展时，它会带来包含一些有用函数的模块，这些函数可以帮助您将 Oracle 应用程序移植到 PostgreSQL。Orafce 提供了 NVL 函数，您可以使用该函数从 Oracle 迁移代码，而无需更改代码。\n\n以下示例显示了 Orafce NVL 如何处理 NULL 输入，但不像 Oracle 数据库那样将空字符串视为 NULL：\n\n```\npostgres=> SELECT nvl(null, 'A'::text) res;\n res\n-----\n A\n(1 row)\n\npostgres=> SELECT nvl('', 'A'::text) res;\n res\n-----\n\n(1 row)\n```\n\n因此，如果您在代码迁移或开发过程中使用 Orafce NVL 函数，则必须确保处理字符变化或文本数据类型的空字符串，如下例所示：\n\n```\npostgres=> SELECT nvl(nullif('', ''), 'A'::text) res;\n res\n-----\n A\n(1 row)\n```\n\n#### **模拟 NVL 函数作为字符变化或文本参数的包装器**\n\n在 COALESCE 或 Orafce NVL 中为每个字符变化或文本数据输入添加 NULLIF 函数可能会使代码迁移过程繁琐且耗时。为了降低这种风险，一种变通方法是在 PostgreSQL 中创建一个包装器函数，该函数将空字符串解析为 NULL，同时提高性能。\n\n下面显示了 NVL 函数的包装器代码，其中包含两个文本类型的参数：\n```\nCREATE OR REPLACE FUNCTION nvl(text, text)\nRETURNS text\nLANGUAGE sql\nAS $$\n select coalesce(NULLIF($1,''), NULLIF($2,''))\n$$;\n```\n\n让我们测试一下我们创建的 NVL 包装器函数，看看它是否可以处理 NULL 以及空字符串：\n\n```\npostgres=> SELECT coalesce('', 'A') res;\n res\n -----\n\n(1 row)\n\n\npostgres=> SELECT nvl(NULL, 'A') res;\n res\n -----\n A\n(1 row)\n\n\npostgres=> SELECT nvl('', 'A') res;\n res\n -----\n A\n(1 row)\n```\n由于空字符串可能是 Oracle 中字符或文本数据类型的输入参数，因此您可以创建前面的包装器函数来模拟 Oracle NVL 函数的类似行为并加快代码迁移过程。\n\n由于 PostgreSQL 中的空字符串行为，我们创建的 NVL 包装器函数仅适用于字符变化或文本数据类型。对于其他数据类型，您可以选择 COALESCE 或 Orafce NVL 函数。\n\n#### **处理 DECODE 和 CASE 表达式**\n\n像 CASE 和 DECODE 这样的表达式可以是许多动态查询或条件表达式的一部分，您需要确保它对于 NULL 和 “as input 保持类似的行为。请参阅以下 Oracle 代码：\n\n```\nSET serveroutput on;\n\nDECLARE\n var varchar2(100);\n res varchar2(100);\n\nBEGIN\n--CASE Expression - Input as NULL\n var := NULL;\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res\n FROM dual;\ndbms_output.put_line('CASE Expression - Input as NULL, Result is '|| res);\n\n--CASE Expression - Input as Empty String\n var := '';\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res\n FROM dual;\ndbms_output.put_line('CASE Expression - Input as empty string, Result is '|| res);\n\n--DECODE Expression - Input as NULL\n var := NULL;\n SELECT DECODE(var, null, 'NULL', 'NOT NULL') \n INTO res \n FROM dual;\ndbms_output.put_line('DECODE Expression - Input as NULL, Result is '|| res);\n\n--DECODE Expression - Input as Empty String\n var := '';\n SELECT DECODE(var, null, 'NULL', 'NOT NULL') \n INTO res \n FROM dual;\ndbms_output.put_line('DECODE Expression - Input as empty string, Result is '|| res);\n\nEND;\n/\n\n```\n\n您将获得以下输出：\n\n```\nCASE Expression - Input as NULL, Result is NULL\nCASE Expression - Input as empty string, Result is NULL\nDECODE Expression - Input as NULL, Result is NULL\nDECODE Expression - Input as empty string, Result is NULL\n```\nPostgreSQL 引擎提供了额外的函数和扩展来缓解空字符串条件求值，并提供与 Oracle 引擎相同的行为：\n\n```\nSET client_min_messages = debug;\n\nDO $$\nDECLARE\n var varchar(100);\n res varchar(100);\n\nBEGIN\n--CASE Expression - Input as NULL\n var := NULL;\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res;\nRaise debug using message := concat_ws('', 'CASE Expression - Input as NULL, Result is ', res);\n\n--CASE Expression - Input as Empty String\n var := '';\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res;\nRaise debug using message := concat_ws('', 'CASE Expression - Input as empty string, Result is ', res);\n\n--DECODE Expression – No DECODE function in PostgreSQL\n\nEND $$;\n```\n\n您将获得以下输出：\n\n```\nDEBUG: CASE Expression - Input as NULL, Result is NULL\nDEBUG: CASE Expression - Input as empty string, Result is NOT NULL\n```\n\nPostgreSQL 中不存在像 DECODE 这样的函数。您需要手动转换为 CASE 表达式。您也可以使用 Orafce DECODE 函数。\n\n在 Oracle 中，DECODE 和 CASE 表达式都将 NULL 字符串和空字符串视为相同，在将它们转换为 PostgreSQL 时需要正确处理。\n\n有了这样的理解，让我们来探索在不破坏功能的情况下将 CASE 或 DECODE 迁移到 PostgreSQL 的各种选项和最佳实践。\n\n**选项 1：将 DECODE 转换为 CASE 表达式**\n\n从 Oracle 迁移代码时，您可以在 PostgreSQL 中将所有 DECODE 函数转换为传统的 CASE 表达式。但是当空字符串可能作为 CASE 表达式的输入出现时，您必须始终记住 CASE 的正确实现。\n\n以下是 Oracle 代码：\n\n```\nSELECT DECODE(var, null, 'NULL', 'NOT NULL') \n FROM DUAL;\n```\n\n以下是在 PostgreSQL 中编写搜索 CASE 表达式的正确方法：\n\n```\nSELECT CASE WHEN nullif(var, '') IS null THEN 'NULL' \n ELSE 'NOT NULL'\n END;\n```\n\n**选项 2：在 PostgreSQL 中使用 Orafce DECODE 函数**\n\n让我们使用不同类型的输入参数来测试 Orafce 扩展提供的 DECODE 函数，并学习如何正确处理空字符串。\n\n当输入为非 NULL 字符串时，Oracle DECODE 和 Orafce DECODE 提供相同的输出。\n\n以下是 Oracle 代码：\n\n```\nSQL> SELECT DECODE('one', null, 'NULL', 'NOT NULL') res FROM DUAL;\n\n RES\n--------\nNOT NULL\n```\n\n以下是 PostgreSQL 代码：\n\n```\npostgres=> SELECT DECODE('one'::text, null, 'NULL', 'NOT NULL') res;\n res\n----------\n NOT NULL\n(1 row)\n```\n\n当输入为 NULL 时，Oracle DECODE 和 Orafce DECODE 提供相同的输出。\n\n以下是 Oracle 代码：\n\n```\nSQL> SELECT DECODE(null, null, 'NULL', 'NOT NULL') res FROM DUAL;\n RES\n ----\n NULL\n```\n\n以下是 PostgreSQL 代码：\n\n```\npostgres=> SELECT DECODE(null::text, null, 'NULL', 'NOT NULL') res;\n res\n----------\n NULL\n (1 row)\n```\n\n当输入为空字符串时，Oracle DECODE 和 Orafce DECODE 提供不同的输出。\n\n以下是 Oracle 代码：\n\n```\nSQL> SELECT DECODE('', null, 'NULL', 'NOT NULL') res FROM DUAL;\n RES\n ----\n NULL\n```\n\n以下是 PostgreSQL 代码：\n\n```\npostgres=> SELECT DECODE(''::text, null, 'NULL', 'NOT NULL') res;\n res\n----------\nNOT NULL\n(1 row)\n```\n\n在这种情况下，您必须转换为 CASE（如前所述），或者将 NULLIF 与 DECODE 结合使用：\n\n```\npostgres=> SELECT DECODE(nullif('', ''), null, 'NULL', 'NOT NULL') res;\n res\n----------\n NULL\n (1 row)\n\n```\n\n#### **结论**\n在将数据库从 Oracle 迁移到 PostgreSQL 时，处理空字符串并使用不同的运算符、表达式或函数（如 IS NULL、NVL、CASE 和 DECODE）进行求值应该是一个重要的考虑因素。在这篇博文中，我们讨论了从 Oracle 迁移到 PostgreSQL 期间处理空字符串重要性和可行的解决方案。我们的示例涵盖了处理空字符串的不同变体，以了解迁移到 PostgreSQL 的总体组成部分、影响和注意事项。\n\n如果您对这篇博文有任何疑问或建议，请留言。\n\n#### **关于作者**\n\n![image.png](https://dev-media.amazoncloud.cn/200949c22aa8440684eaaa8c043caa23_image.png)\n\n**Sashikanta Pattanayak** 是 Amazon 专业服务团队的首席顾问。他与客户合作，在 Amazon Cloud 中构建可扩展、高度可用且安全的解决方案。他擅长同构和异构数据库迁移。\n\n![image.png](https://dev-media.amazoncloud.cn/2df0d0ec939a4c36b6f5540aad96fb51_image.png)\n\n**Deepak Mahto** 曾是印度 Amazon Proserve 团队的顾问。他曾担任数据库迁移主管，帮助并支持客户从商业引擎迁移到 Amazon RDS。他热衷于自动化，并设计和实施了多个与数据库或迁移相关的工具。\n\n![image.png](https://dev-media.amazoncloud.cn/4921ad372a25417f9011df275173db5d_image.png)\n\n**Vinay Paladi** 是 Amazon Web Services 专业服务团队的数据库顾问。他是一名数据库迁移专家，帮助并支持客户构建高可用性、经济高效的数据库解决方案，并将其商业引擎迁移到 Amazon Cloud。他热衷于构建创新的解决方案，以加快数据库向云的迁移过程","render":"Amazon Cloud 中的 Oracle 到 PostgreSQL 迁移可能是一个多阶段流程：从评估阶段直到移交阶段，涉及到不同的技术和技能。Amazon 提供的服务可让 PostgreSQL 数据库部署以经济高效的方式轻松设置、管理和针对云进行扩展。这些服务包括<a href=\"https://aws.amazon.com/rds/postgresql/\" target=\"_blank\"> Amazon Relational Database Service (Amazon RDS) for PostgreSQL</a> 和 <a href=\"https://aws.amazon.com/rds/aurora/details/postgresql-details/\" target=\"_blank\">Amazon Aurora PostgreSQL 兼容版</a>。\n在架构或代码转换阶段，我们可以使用 <a href=\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html\" target=\"_blank\">Amazon Schema Conversion Tool</a> (Amazon SCT) 转换大部分代码。有关 Amazon SCT 提出的代码转换异常，我们可以参考<a href=\"https://aws.amazon.com/prescriptive-guidance/?apg-all-cards.sort-by=item.additionalFields.sortText&apg-all-cards.sort-order=desc&awsf.apg-new-filter=*all&awsf.apg-content-type-filter=*all&awsf.apg-code-filter=*all&awsf.apg-category-filter=*all&awsf.apg-rtype-filter=*all&awsf.apg-isv-filter=*all&awsf.apg-product-filter=*all&awsf.apg-env-filter=*all\" target=\"_blank\">迁移模式</a>和 <a href=\"https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook-12.4.pdf\" target=\"_blank\">Oracle 到 PostgreSQL 的迁移行动手册</a>。\n将代码从 Oracle 转换为兼容 PostgreSQL 的引擎可能涉及多个对您的整体迁移成功很重要的极端情况。在从 Oracle 迁移到 PostgreSQL 时，使用所有兼容 NULL 的运算符或表达式处理空字符串 (“) 对于实现转换代码的整体功能接受度并产生整体影响至关重要。\nOracle 数据库将空字符串和 NULL 视为相同。但是，在 PostgreSQL 中，空字符串和 NULL 是不同的。如果在 Oracle 上运行工作负载，则可以使用业务逻辑实现过程代码，假设在执行任何比较或 NULL 检查时将空字符串视为 NULL。在这篇博文中，我们将介绍在 PostgreSQL 中处理空字符串的不同变体以及迁移期间可行的解决方案。我们举例说明迁移的总体组成部分、影响和注意事项。\n<h4><a id=\"NULL__8\"></a>NULL 字符串与空字符串</h4>\nOracle 数据库将 NULL 字符串和空字符串视为相同；您可以在任何操作中互换使用空字符串和 NULL 字符串。Oracle 中的以下条件语句全部计算为 NULL：\n<pre><code class=\"lang-\">SQL> set NULL (NULL);\n \nSQL> SELECT length(NULL) res FROM DUAL;\n \n RES\n\t----------\n\t (NULL)\n \n \nSQL> SELECT length('') res FROM DUAL;\n \n RES\n\t----------\n\t (NULL)\n</code></pre>\n但是，在 PostgreSQL 中，NULL 表示值未知或未给出，空字符串是长度为 0 的字符串。因此，NULL 和空字符串的解释、表示和运算方式不同。PostgreSQL 数据库将空字符串视为可识别的值，其处理方式与其他可用字符类似：\n<pre><code class=\"lang-\">postgres=> \\pset null (NULL)\n\npostgres=> SELECT length(NULL) res;\n\n res\n--------\n (NULL)\n(1 row)\n\n\npostgres=> SELECT length('') res;\n\n res\n-----\n 0\n(1 row)\n</code></pre>\n下表总结了这些不同。\n<img src=\"https://dev-media.amazoncloud.cn/ee8ee11ad9f04032ac2698fa9028d3ff_image.png\" alt=\"image.png\" />\n请注意，PostgreSQL 中 NULL 字符串和空字符串的区别如下：\n<ul>\n<li>NULL 可以分配给任何类型，而空字符串则不同，空字符串与日期或数值字段不兼容。请参见以下示例代码：</li>\n</ul>\n<pre><code class=\"lang-\">postgres=> select null::int, null::date;\n int4 | date\n------+------\n |\n(1 row)\n\npostgres=> select ''::int;\nERROR: invalid input syntax for type integer: ""\nLINE 1: select ''::int;\n^\n\npostgres=> select ''::date;\nERROR: invalid input syntax for type date: ""\nLINE 1: select ''::date;\n^\n</code></pre>\n<ul>\n<li>NULL 是一个未知值。它没有值，而空字符串则不同，空字符串是一个值，但却是一个空值。</li>\n<li>查找 NULL 的大小或长度会生成 NULL 结果。空字符串是长度为 0 的字符串。</li>\n<li>NULL = NULL 的计算结果为 NULL，而 ”=” 则不同，其计算结果为 TRUE。</li>\n<li>NULL 不能与任何内容进行比较，甚至不能与其自身进行比较，而空字符串则可以比较。</li>\n</ul>\nOracle 与 PostgreSQL 最大的一个区别是 NULL 和空字符串在复合唯一索引和约束方面的操作方式。PostgreSQL 允许在唯一索引中包含多行 NULL。相反，Oracle 在复合唯一约束或唯一索引中限制多个 NULL 实例和空字符串。当遇到空字符串时，Oracle 和 PostgreSQL 都会为多个条目引发错误。\n以下是使用 Oracle 的示例：\n<pre><code class=\"lang-\">SQL> CREATE TABLE test_unq(c1 integer, c2 varchar2(10));\n\nSQL> ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);\n\nSQL> insert into test_unq values(1, null);\n\n1 row created.\n\nSQL> insert into test_unq values(1, null);\ninsert into test_unq values(1, null)\n*\nERROR at line 1:\nORA-00001: unique constraint (TESTORA.IDX_TEST_UNQ) violated\n</code></pre>\n由于 Oracle 将空字符串视为 NULL，因此违反了唯一约束或唯一索引多个空字符串条目的约定：\n<pre><code class=\"lang-\">SQL> insert into test_unq values(2, '');\n\n1 row created.\n\nSQL> insert into test_unq values(2, '');\ninsert into test_unq values(2, '')\n*\nERROR at line 1:\nORA-00001: unique constraint (TESTORA.TEST_UNQ_C) violated\n\n</code></pre>\n以下是使用 PostgreSQL 的相同示例：\n<pre><code class=\"lang-\">postgres=> CREATE TABLE test_unq(c1 integer, c2 character varying(10));\n\npostgres=> ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);\n\npostgres=> insert into test_unq values(1, null);\nINSERT 0 1\n\npostgres=> insert into test_unq values(1, null);\nINSERT 0 1\n</code></pre>\nPostgreSQL 数据库将空字符串和 NULL 视为不同。所有空字符串都是相等的，因此违反了唯一约束或唯一索引多个空字符串条目的约定：\n<pre><code class=\"lang-\">postgres=> insert into test_unq values(2, '');\nINSERT 0 1\n\npostgres=> insert into test_unq values(2, '');\nERROR: duplicate key value violates unique constraint "test_unq_c"\nDETAIL: Key (c1, c2)=(2, ) already exists.\n</code></pre>\n<h4><a id=\"_PostgreSQL__141\"></a>在 PostgreSQL 中处理空字符串</h4>\n从前面的讨论中可以明显看出，在从 Oracle 迁移到 PostgreSQL 时，空字符串的处理非常重要且至关重要。\n让我们创建一个示例表并向其插入数据。您可以使用以下 SQL 语句在 Oracle 和 PostgreSQL 中创建表并插入数据：\n<pre><code class=\"lang-\">CREATE TABLE Test_Empty_or_Null(\n tid INTEGER,\n tname VARCHAR(10),\n txdate DATE\n);\n\n-- insert statement #1\nINSERT INTO Test_Empty_or_Null VALUES(1, 'Test1', current_date);\n\n-- insert statement #2\nINSERT INTO Test_Empty_or_Null VALUES(2, NULL, current_date);\n\n-- insert statement #3\nINSERT INTO Test_Empty_or_Null VALUES(3, '', current_date);\n\nCOMMIT;\n\n</code></pre>\n在 Oracle 中，由于空字符串被视为 NULL，因此前面的插入语句 #2 和 #3 将为表中的列 tname 存储 NULL。但是，在 PostgreSQL 中，插入语句 #2 将为表存储 NULL，插入语句 #3 将为表存储空字符串。\n如果使用 IS NULL 或 IS NOT NULL 运算符选择行，则可以看出 Oracle 和 PostgreSQL 的区别。\n以下是 Oracle 代码：\n<pre><code class=\"lang-\">SQL> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n\n TID TNAME TXDATE\n---------- ---------- ---------\n 2 \t\t \t 20-AUG-21\n 3 \t\t \t 20-AUG-21\n\n</code></pre>\n以下是 PostgreSQL 代码：\n<pre><code class=\"lang-\">postgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n tid \t| tname | txdate\n -----+-------+------------\n 2 \t| \t | 2021-08-20\n(1 row)\n</code></pre>\n如果要在 PostgreSQL 中选择所有包含 NULL 或空字符串的行，则必须在查询的 WHERE 子句中显式添加空字符串条件：\n<pre><code class=\"lang-\">postgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL OR tname = '';\n tid \t| tname | txdate\n -----+-------+------------\n 2 \t| \t | 2021-08-20\n 3 \t| \t | 2021-08-20\n(2 rows) \n</code></pre>\n或者，您可以在 PostgreSQL 中使用 COALESCE 或 NULLIF 等空处理函数来处理包含空字符串的情况。\n<h4><a id=\"COALESCE__206\"></a>COALESCE 函数</h4>\nCOALESCE 函数返回其第一个非 NULL 参数。仅当所有参数均为 NULL 时才返回 NULL。您可以将 <code>WHERE tname IS NULL</code> 筛选条件调整为<code> WHERE coalesce(tname, '') = ''</code> 以获取 <code>tname</code> 为空字符串或未知的所有行。\n但是，更改 SQL 查询中的 WHERE 子句以支持该功能可能会影响查询的运行计划。数据库可能不会优化计划，因为特定的筛选器已更改，并且不会使用在此列上创建的任何索引。您的查询可能会变慢。在这些情况下，您必须分析查询运行计划，重新访问创建的索引，并根据需要在表上创建<a href=\"https://www.postgresql.org/docs/14/indexes-partial.html\" target=\"_blank\">部分索引</a>以优化查询。查询优化不在这篇博文的讨论范围内。\n以下代码是 COALESCE 函数的示例：\nPostgreSQL\n<pre><code class=\"lang-\">postgres=> SELECT * FROM Test_Empty_or_Null WHERE coalesce(tname, '') = '';\n\n\t tid\t| tname | txdate\n\t ----+-------+------------\n 2 \t| \t | 2021-08-20\n 3 \t| \t | 2021-08-20\n (2 rows)\n</code></pre>\nOracle：\n<pre><code class=\"lang-\">SQL> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n\n TID TNAME TXDATE\n ---------- ---------- ---------\n 2 \t\t 20-AUG-21\n 3 \t\t 20-AUG-21\n</code></pre>\n使用 COALESCE 处理空字符串的 IS NULL 或 IS NOT NULL 需要进行大量代码更改。更简单的方法是使用 NULLIF 函数。\n<h4><a id=\"NULLIF__239\"></a>NULLIF 函数</h4>\n如果两个参数相同，则 <a href=\"https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-NULLIF\" target=\"_blank\">NULLIF</a> 将返回 NULL。当两个参数不同时，它返回第 1 个参数。我们用它来比较输入表达式和空字符串并返回 NULL：\n<pre><code class=\"lang-\">postgres=> \\pset null (NULL)\n\npostgres=> SELECT NULLIF('', '') res;\n res\n--------\n (NULL)\n(1 row)\n</code></pre>\n对于可能遇到空字符串 (”) 作为输入的情况，我们会使用 NULLIF 转换原生 PostgreSQL 函数。例如，如果您有一个像 <code>“tname IS NULL” </code>这样的条件，其中 <code>“tname”</code> 可以包含 NULL，那么您可以在 PostgreSQL 中更改 <code>“nullif(tname, '') IS NULL”</code>。\n<pre><code class=\"lang-\">postgres=> SELECT * FROM Test_Empty_or_Null WHERE tname IS NULL;\n\t tid \t| tname | txdate\n\t -----+-------+------------\n 2 \t| \t | 2021-08-20\n (1 row)\n\n/* NULLIF 作为表达式的一部分是必需的，因为 PostgreSQL “IS NULL” 运算符不处理空字符串 */\n\npostgres=> SELECT * FROM Test_Empty_or_Null WHERE nullif(tname, '') IS NULL;\n\t tid \t| tname | txdate\n\t -----+-------+------------\n\t\t2 \t| \t | 2021-08-20\n\t\t3 \t| \t | 2021-08-20\n(2 rows) \n</code></pre>\n对于所有此类 NULL 和空字符串的情况，让我们深入研究其他需要适当注意以将 Oracle 过程代码移植到 PostgreSQL 的数据库表达式或函数。\n<h4><a id=\"NULL__274\"></a>NULL 处理函数和空字符串的挑战</h4>\nOracle 和 PostgreSQL 中的 NULL 处理函数在任何比较或表达式中的工作方式都是相同的。但是，由于空字符串是 Oracle 中表示 NULL 的另一种方式，因此使用 NULL 或空字符串操作 NULL 处理函数时没有观察到差异。但是在 PostgreSQL 数据库中，NULL 处理函数与空字符串结合使用的计算结果为 FALSE。这在从 Oracle 迁移到 PostgreSQL 以模拟 NULL 处理函数在处理空字符串时的相同行为时产生了挑战。\n下表总结了 Oracle 和 PostgreSQL 关于空字符串的整体行为。\n<img src=\"https://dev-media.amazoncloud.cn/cec4b359447a4970b12a750f3d8d0c08_image.png\" alt=\"image.png\" />\n现在，我们已经更好地了解了 Oracle 和 PostgreSQL 的差异，接下来我们可以逐步了解一些在转换阶段使用的变通方法和最佳实践。\n<h4><a id=\"_IS_NULL__IS_NOT_NULL_285\"></a>处理 IS NULL 和 IS NOT NULL</h4>\n如果您在 Oracle 中多次使用诸如 IS NULL 和 IS NOT NULL 之类的条件表达式作为过程代码的一部分，则必须验证它们在 PostgreSQL 中是否可以处理空字符串。如果在表达式求值期间列或变量值可能变成空字符串，请在 PostgreSQL 中使用 NULLIF 函数。\n在从 Oracle 到 PostgreSQL 数据库的代码迁移期间，只有字符变化的数据类型才需要添加 NULLIF。这与 NUMERIC 或 DATE 等其他数据类型不兼容。\n<h4><a id=\"_NVL__COALESCE_291\"></a>处理 NVL 和 COALESCE</h4>\nPostgreSQL 数据库没有内置的 NVL 函数，而是支持 COALESCE 函数，后者是符合 ANSII 的 NVL 函数。COALESCE 函数非常适合字符串以外的数据类型。这是因为字符串可以接受空字符串。对于可能接受空字符串作为输入的情况，必须使用 PostgreSQL COALESCE 函数小心处理它们。\n以下是 Oracle 代码：\n<pre><code class=\"lang-\">SQL> SELECT coalesce(NULL, 'A') res FROM DUAL;\n\n RES\n----------\n A\n</code></pre>\n以下是 PostgreSQL 代码：\n<pre><code class=\"lang-\">postgres=> SELECT coalesce(NULL, 'A') res;\n res\n -----\n A\n(1 row)\n</code></pre>\n以下示例显示了将空字符串作为输入时 COALESCE 的功能有何不同。\n以下是 Oracle 代码：\n<pre><code class=\"lang-\">SQL> SELECT coalesce('', 'A') res FROM DUAL;\n\n RES\n----------\n A\n</code></pre>\n以下是 PostgreSQL 代码：\n<pre><code class=\"lang-\">postgres=> SELECT coalesce('', 'A') res;\n res\n -----\n\n (1 row)\n</code></pre>\n尽管 COALESCE 在 PostgreSQL 中的行为符合标准，但在从 Oracle 迁移时，它并不总是基于输入值或变量值兼容。让我们了解一下 COALESCE 处理空字符串的解决方法。以下是 PostgreSQL 代码：\n<pre><code class=\"lang-\">postgres=> SELECT coalesce(nullif('', ''), 'a') res;\n res\n -----\n a\n(1 row)\n</code></pre>\n<h4><a id=\"Oracle_NVL__347\"></a>Oracle NVL 函数</h4>\n<a href=\"https://github.com/orafce/orafce\" target=\"_blank\">Orafce</a> 是 <a href=\"https://aws.amazon.com/rds/postgresql/\" target=\"_blank\">Amazon Relational Database Service (Amazon RDS) for PostgreSQL</a> 和 <a href=\"https://aws.amazon.com/rds/aurora/postgresql-features/\" target=\"_blank\">Amazon Aurora PostgreSQL</a> 兼容版中支持的扩展。当您在 PostgreSQL 中创建此扩展时，它会带来包含一些有用函数的模块，这些函数可以帮助您将 Oracle 应用程序移植到 PostgreSQL。Orafce 提供了 NVL 函数，您可以使用该函数从 Oracle 迁移代码，而无需更改代码。\n以下示例显示了 Orafce NVL 如何处理 NULL 输入，但不像 Oracle 数据库那样将空字符串视为 NULL：\n<pre><code class=\"lang-\">postgres=> SELECT nvl(null, 'A'::text) res;\n res\n-----\n A\n(1 row)\n\npostgres=> SELECT nvl('', 'A'::text) res;\n res\n-----\n\n(1 row)\n</code></pre>\n因此，如果您在代码迁移或开发过程中使用 Orafce NVL 函数，则必须确保处理字符变化或文本数据类型的空字符串，如下例所示：\n<pre><code class=\"lang-\">postgres=> SELECT nvl(nullif('', ''), 'A'::text) res;\n res\n-----\n A\n(1 row)\n</code></pre>\n<h4><a id=\"_NVL__377\"></a>模拟 NVL 函数作为字符变化或文本参数的包装器</h4>\n在 COALESCE 或 Orafce NVL 中为每个字符变化或文本数据输入添加 NULLIF 函数可能会使代码迁移过程繁琐且耗时。为了降低这种风险，一种变通方法是在 PostgreSQL 中创建一个包装器函数，该函数将空字符串解析为 NULL，同时提高性能。\n下面显示了 NVL 函数的包装器代码，其中包含两个文本类型的参数：\n<pre><code class=\"lang-\">CREATE OR REPLACE FUNCTION nvl(text, text)\nRETURNS text\nLANGUAGE sql\nAS $$\n select coalesce(NULLIF($1,''), NULLIF($2,''))\n$$;\n</code></pre>\n让我们测试一下我们创建的 NVL 包装器函数，看看它是否可以处理 NULL 以及空字符串：\n<pre><code class=\"lang-\">postgres=> SELECT coalesce('', 'A') res;\n res\n -----\n\n(1 row)\n\n\npostgres=> SELECT nvl(NULL, 'A') res;\n res\n -----\n A\n(1 row)\n\n\npostgres=> SELECT nvl('', 'A') res;\n res\n -----\n A\n(1 row)\n</code></pre>\n由于空字符串可能是 Oracle 中字符或文本数据类型的输入参数，因此您可以创建前面的包装器函数来模拟 Oracle NVL 函数的类似行为并加快代码迁移过程。\n由于 PostgreSQL 中的空字符串行为，我们创建的 NVL 包装器函数仅适用于字符变化或文本数据类型。对于其他数据类型，您可以选择 COALESCE 或 Orafce NVL 函数。\n<h4><a id=\"_DECODE__CASE__418\"></a>处理 DECODE 和 CASE 表达式</h4>\n像 CASE 和 DECODE 这样的表达式可以是许多动态查询或条件表达式的一部分，您需要确保它对于 NULL 和 “as input 保持类似的行为。请参阅以下 Oracle 代码：\n<pre><code class=\"lang-\">SET serveroutput on;\n\nDECLARE\n var varchar2(100);\n res varchar2(100);\n\nBEGIN\n--CASE Expression - Input as NULL\n var := NULL;\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res\n FROM dual;\ndbms_output.put_line('CASE Expression - Input as NULL, Result is '|| res);\n\n--CASE Expression - Input as Empty String\n var := '';\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res\n FROM dual;\ndbms_output.put_line('CASE Expression - Input as empty string, Result is '|| res);\n\n--DECODE Expression - Input as NULL\n var := NULL;\n SELECT DECODE(var, null, 'NULL', 'NOT NULL') \n INTO res \n FROM dual;\ndbms_output.put_line('DECODE Expression - Input as NULL, Result is '|| res);\n\n--DECODE Expression - Input as Empty String\n var := '';\n SELECT DECODE(var, null, 'NULL', 'NOT NULL') \n INTO res \n FROM dual;\ndbms_output.put_line('DECODE Expression - Input as empty string, Result is '|| res);\n\nEND;\n/\n\n</code></pre>\n您将获得以下输出：\n<pre><code class=\"lang-\">CASE Expression - Input as NULL, Result is NULL\nCASE Expression - Input as empty string, Result is NULL\nDECODE Expression - Input as NULL, Result is NULL\nDECODE Expression - Input as empty string, Result is NULL\n</code></pre>\nPostgreSQL 引擎提供了额外的函数和扩展来缓解空字符串条件求值，并提供与 Oracle 引擎相同的行为：\n<pre><code class=\"lang-\">SET client_min_messages = debug;\n\nDO $$\nDECLARE\n var varchar(100);\n res varchar(100);\n\nBEGIN\n--CASE Expression - Input as NULL\n var := NULL;\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res;\nRaise debug using message := concat_ws('', 'CASE Expression - Input as NULL, Result is ', res);\n\n--CASE Expression - Input as Empty String\n var := '';\n SELECT CASE WHEN var IS NULL THEN 'NULL' \n ELSE 'NOT NULL'\n END \n INTO res;\nRaise debug using message := concat_ws('', 'CASE Expression - Input as empty string, Result is ', res);\n\n--DECODE Expression – No DECODE function in PostgreSQL\n\nEND $$;\n</code></pre>\n您将获得以下输出：\n<pre><code class=\"lang-\">DEBUG: CASE Expression - Input as NULL, Result is NULL\nDEBUG: CASE Expression - Input as empty string, Result is NOT NULL\n</code></pre>\nPostgreSQL 中不存在像 DECODE 这样的函数。您需要手动转换为 CASE 表达式。您也可以使用 Orafce DECODE 函数。\n在 Oracle 中，DECODE 和 CASE 表达式都将 NULL 字符串和空字符串视为相同，在将它们转换为 PostgreSQL 时需要正确处理。\n有了这样的理解，让我们来探索在不破坏功能的情况下将 CASE 或 DECODE 迁移到 PostgreSQL 的各种选项和最佳实践。\n选项 1：将 DECODE 转换为 CASE 表达式\n从 Oracle 迁移代码时，您可以在 PostgreSQL 中将所有 DECODE 函数转换为传统的 CASE 表达式。但是当空字符串可能作为 CASE 表达式的输入出现时，您必须始终记住 CASE 的正确实现。\n以下是 Oracle 代码：\n<pre><code class=\"lang-\">SELECT DECODE(var, null, 'NULL', 'NOT NULL') \n FROM DUAL;\n</code></pre>\n以下是在 PostgreSQL 中编写搜索 CASE 表达式的正确方法：\n<pre><code class=\"lang-\">SELECT CASE WHEN nullif(var, '') IS null THEN 'NULL' \n ELSE 'NOT NULL'\n END;\n</code></pre>\n选项 2：在 PostgreSQL 中使用 Orafce DECODE 函数\n让我们使用不同类型的输入参数来测试 Orafce 扩展提供的 DECODE 函数，并学习如何正确处理空字符串。\n当输入为非 NULL 字符串时，Oracle DECODE 和 Orafce DECODE 提供相同的输出。\n以下是 Oracle 代码：\n<pre><code class=\"lang-\">SQL> SELECT DECODE('one', null, 'NULL', 'NOT NULL') res FROM DUAL;\n\n RES\n--------\nNOT NULL\n</code></pre>\n以下是 PostgreSQL 代码：\n<pre><code class=\"lang-\">postgres=> SELECT DECODE('one'::text, null, 'NULL', 'NOT NULL') res;\n res\n----------\n NOT NULL\n(1 row)\n</code></pre>\n当输入为 NULL 时，Oracle DECODE 和 Orafce DECODE 提供相同的输出。\n以下是 Oracle 代码：\n<pre><code class=\"lang-\">SQL> SELECT DECODE(null, null, 'NULL', 'NOT NULL') res FROM DUAL;\n RES\n ----\n NULL\n</code></pre>\n以下是 PostgreSQL 代码：\n<pre><code class=\"lang-\">postgres=> SELECT DECODE(null::text, null, 'NULL', 'NOT NULL') res;\n res\n----------\n NULL\n (1 row)\n</code></pre>\n当输入为空字符串时，Oracle DECODE 和 Orafce DECODE 提供不同的输出。\n以下是 Oracle 代码：\n<pre><code class=\"lang-\">SQL> SELECT DECODE('', null, 'NULL', 'NOT NULL') res FROM DUAL;\n RES\n ----\n NULL\n</code></pre>\n以下是 PostgreSQL 代码：\n<pre><code class=\"lang-\">postgres=> SELECT DECODE(''::text, null, 'NULL', 'NOT NULL') res;\n res\n----------\nNOT NULL\n(1 row)\n</code></pre>\n在这种情况下，您必须转换为 CASE（如前所述），或者将 NULLIF 与 DECODE 结合使用：\n<pre><code class=\"lang-\">postgres=> SELECT DECODE(nullif('', ''), null, 'NULL', 'NOT NULL') res;\n res\n----------\n NULL\n (1 row)\n\n</code></pre>\n<h4><a id=\"_618\"></a>结论</h4>\n在将数据库从 Oracle 迁移到 PostgreSQL 时，处理空字符串并使用不同的运算符、表达式或函数（如 IS NULL、NVL、CASE 和 DECODE）进行求值应该是一个重要的考虑因素。在这篇博文中，我们讨论了从 Oracle 迁移到 PostgreSQL 期间处理空字符串重要性和可行的解决方案。我们的示例涵盖了处理空字符串的不同变体，以了解迁移到 PostgreSQL 的总体组成部分、影响和注意事项。\n如果您对这篇博文有任何疑问或建议，请留言。\n<h4><a id=\"_623\"></a>关于作者</h4>\n<img src=\"https://dev-media.amazoncloud.cn/200949c22aa8440684eaaa8c043caa23_image.png\" alt=\"image.png\" />\nSashikanta Pattanayak 是 Amazon 专业服务团队的首席顾问。他与客户合作，在 Amazon Cloud 中构建可扩展、高度可用且安全的解决方案。他擅长同构和异构数据库迁移。\n<img src=\"https://dev-media.amazoncloud.cn/2df0d0ec939a4c36b6f5540aad96fb51_image.png\" alt=\"image.png\" />\nDeepak Mahto 曾是印度 Amazon Proserve 团队的顾问。他曾担任数据库迁移主管，帮助并支持客户从商业引擎迁移到 Amazon RDS。他热衷于自动化，并设计和实施了多个与数据库或迁移相关的工具。\n<img src=\"https://dev-media.amazoncloud.cn/4921ad372a25417f9011df275173db5d_image.png\" alt=\"image.png\" />\nVinay Paladi 是 Amazon Web Services 专业服务团队的数据库顾问。他是一名数据库迁移专家，帮助并支持客户构建高可用性、经济高效的数据库解决方案，并将其商业引擎迁移到 Amazon Cloud。他热衷于构建创新的解决方案，以加快数据库向云的迁移过程\n"}

亚马逊云科技解决方案基于行业客户应用场景及技术领域的解决方案

联系亚马逊云科技专家