Handle empty strings when migrating from Oracle to PostgreSQL

{"value":"An Oracle-to-PostgreSQL migration in the AWS Cloud can be a multistage process with different technologies and skills involved, starting from the assessment stage to the cutover stage. AWS offers services that make PostgreSQL database deployments easy to set up, manage, and scale for the cloud in a cost-efficient manner. These services are [Amazon Relational Database Service (Amazon RDS) for PostgreSQL](https://aws.amazon.com/rds/postgresql/) and [Amazon Aurora PostgreSQL-Compatible Edition](https://aws.amazon.com/rds/aurora/details/postgresql-details/).\n\nDuring the schema or code conversion phase, we can use [AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) (AWS SCT) to convert the majority of the code. For code conversion exceptions raised by AWS SCT, we can refer to [migration patterns](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) and the [Oracle to PostgreSQL migration playbook](https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook-12.4.pdf).\n\nConverting code from Oracle to a PostgreSQL-compatible engine may involve multiple corner cases that are important for your overall migration success. Handling empty strings (”) with all NULL-compatible operators or expressions while migrating from Oracle to PostgreSQL is vital to achieve overall functional acceptance on the converted code and have a holistic impact.\n\nOracle databases treat empty strings and NULL as the same. However, in PostgreSQL, empty strings and NULL are different. If you’re running workloads on Oracle, you might implement procedural code with business logic, assuming that empty strings are treated as NULL when performing any comparison or NULL check. In this post, we cover different variants of handling empty strings in PostgreSQL and viable solutions during migration. We show examples to understand the overall components, impacts, and considerations of migration.\n\n#### **NULL vs. empty strings**\nOracle databases treat NULL and empty strings as the same; you can use empty strings and NULL interchangeably in any operation. The following conditional statements in Oracle are all evaluated to NULL:\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```\nHowever, in PostgreSQL, NULL means the value is unknown or not given, and an empty string is a string of 0 length. Therefore, NULL and empty strings are interpreted, represented, and operated differently. A PostgreSQL database treats empty strings as an identifiable value that is processed similar to other available characters:\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```\nThe following table summarizes these differences.\n\n\n\nNote the following differences between NULL and empty strings in PostgreSQL:\n- NULL can be assigned to any type, as opposed to empty strings, which aren’t compatible with date or numerical fields. See the following example code:\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- NULL is an unknown value. It doesn’t have a value, as opposed to an empty string, which is a value but an empty one.\n- Finding the size or length of NULL results in NULL. An empty string is a string of 0 length.\n- NULL = NULL results in NULL, as opposed to ”=”, which evaluates to TRUE.\n- NULL is not comparable to anything, not even to itself, whereas empty strings can be compared.\n\nOne of the biggest differences between Oracle and PostgreSQL is how NULL and empty strings operate with regards to composite unique indexes and constraints. PostgreSQL allows multiple rows with NULL in unique indexes. In contrast, Oracle restricts multiple NULL instances and empty strings in a composite unique constraint or unique index. When encountering an empty string, both Oracle and PostgreSQL throw an error for multiple entries.\n\nThe following is an example using Oracle :\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```\nBecause Oracle treats empty strings as NULL, multiple entries of empty strings to unique constraints or unique indexes are violated:\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```\nThe following is the same example using PostgreSQL:\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```\nPostgreSQL databases treat empty strings and NULL as different. All empty strings are equal, and therefore multiple entries of empty strings to a unique constraint or unique index are violated:\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#### **Handling empty strings in PostgreSQL**\nFrom the preceding discussion, it’s evident that when migrating from Oracle to PostgreSQL, empty string handling is important and crucial.\n\nLet’s create a sample table and insert data to it. You can use the following SQL statements to create a table and insert data in both Oracle and PostgreSQL:\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```\nIn Oracle, because empty strings are treated as NULL, the preceding insert statements #2 and #3 will store NULL for column ```tname``` in the table. However, in PostgreSQL, the table will store NULL for the #2 and an empty string for the #3 insert statements.\n\nYou can see the difference in Oracle and PostgreSQL if rows are selected with the IS NULL or IS NOT NULL operator.\n\nThe following is the Oracle code:\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```\nThe following is the PostgreSQL code:\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```\nIf you want to select all rows that have NULL or empty strings in PostgreSQL, you must explicitly add the empty string condition in the query’s WHERE clause:\n```\\n 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```\nOptionally, you can use NULL handling functions such as COALESCE or NULLIF in PostgreSQL to deal with scenarios containing empty strings.\n\n##### **COALESCE function**\nThe [COALESCE](https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL) function returns the first of its arguments that is not NULL. NULL is returned only if all arguments are NULL. You can adjust the ```WHERE tname IS NULL``` filter condition to ```WHERE coalesce(tname, '') = ''``` to get all rows whose ```tname``` is either empty or not known.\n\nHowever, changing WHERE clauses in SQL queries to support the functionality might affect the run plan of query. The database might not optimize the plan because the particular filter is changed, and any index created on such column wouldn’t be used. There are chances that your query could be slower. In those cases, you must analyze the query run plan, revisit the indexes created, and create [partial indexes](https://www.postgresql.org/docs/14/indexes-partial.html) if required on the table to tune the query. Query tuning is beyond the scope of this post.\n\nThe following code is an example of the COALESCE function:\n\nPostgreSQL:\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```\nOracle:\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```\nUsing COALESCE to handle IS NULL or IS NOT NULL for empty strings requires a lot of code changes. A simpler way would be to use the NULLIF function.\n\n##### **NULLIF function**\n[NULLIF](https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-NULLIF) returns NULL if both the arguments are same. It returns the 1st argument when both the arguments are different. We use it to compare the input expression with the empty string and return NULL:\n```\\npostgres=> \\\\pset null (NULL)\\n\\npostgres=> SELECT NULLIF('', '') res;\\n res\\n--------\\n (NULL)\\n(1 row)\\n```\nWe transform native PostgreSQL function with NULLIF for cases where we might encounter empty strings (”) as input. For example, if you have a condition like ```“tname IS NULL”``` where ```“tname”``` can contain NULL, then you can change to ```“nullif(tname, '') IS NULL” ```in PostgreSQL.\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 is required as part of expression as PostgreSQL “IS NULL” operator do not handle empty string */\\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```\nWith all such cases of NULL and empty strings, let’s dive deep into other database expressions or functions that require proper attention for porting Oracle procedural code to PostgreSQL.\n\n#### **NULL handling functions and challenges with empty strings**\nNULL handling functions in both Oracle and PostgreSQL work the same in any comparison or expression. However, because an empty string is another way of representing NULL in Oracle, no difference is observed when NULL handling functions are operated with NULL or empty strings. But in a PostgreSQL database, NULL handling functions in conjunction with empty strings evaluate to FALSE. This creates a challenge when migrating from Oracle to PostgreSQL to simulate the same behavior for NULL handling functions when dealing with empty strings.\n\nThe following table summarizes the overall behavior between Oracle and PostgreSQL regarding empty strings.\n\n\n\nNow that we have a better understanding of the differences between Oracle and PostgreSQL, we can walk through some of the workarounds and best practices to use during the conversion phase.\n\n##### **Handling IS NULL and IS NOT NULL**\nIf you’re using conditional expressions like IS NULL and IS NOT NULL multiple times as part of procedural code in Oracle, then you must validate that they will work in PostgreSQL with respect to empty strings. Use the NULLIF function in PostgreSQL for situations where a column or variable value might become an empty string during an expression evaluation.\n\nDuring code migration from your Oracle to PostgreSQL database, adding NULLIF is required only for character varying data types. This is not compatible for other data types like NUMERIC or DATE.\n\n##### **Handling NVL and COALESCE**\nPostgreSQL databases don’t have a built-in NVL function, and instead support the COALESCE function, which is ANSII compliant for NVL. The COALESCE function works perfectly fine for data types other than strings. This is because strings can accept empty strings. For situations that might accept empty strings as input, you must handle them carefully with the PostgreSQL COALESCE function.\n\nThe following is the Oracle code:\n```\\nSQL> SELECT coalesce(NULL, 'A') res FROM DUAL;\\n\\n RES\\n----------\\n A\\n```\nThe following is the PostgreSQL code:\n```\\npostgres=> SELECT coalesce(NULL, 'A') res;\\n res\\n -----\\n A\\n(1 row) \\n```\nThe following example shows how the functionality of COALESCE differs with empty strings as input.\n\nThe following is the Oracle code:\n```\\nSQL> SELECT coalesce('', 'A') res FROM DUAL;\\n\\n RES\\n----------\\n A\\n```\nThe following is the PostgreSQL code:\n```\\npostgres=> SELECT coalesce('', 'A') res;\\n res\\n -----\\n\\n (1 row)\\n```\nAlthough the behavior of COALESCE in PostgreSQL conforms to the standard, it’s not always compatible based on input or variable values while migrating from Oracle. Let’s understand the workaround for COALESCE for empty string handling. The following is the PostgreSQL code:\n```\\npostgres=> SELECT coalesce(nullif('', ''), 'a') res;\\n res\\n -----\\n a\\n(1 row)\\n```\n##### **Orafce NVL function**\nOrafce is an extension supported in [Amazon Relational Database Service (Amazon RDS) for PostgreSQL](https://aws.amazon.com/rds/postgresql/) and [Amazon Aurora PostgreSQL-Compatible Edition](https://aws.amazon.com/rds/aurora/postgresql-features/). When you create this extension in PostgreSQL, it brings modules containing some useful functions that can help with porting your Oracle application to PostgreSQL. Orafce provides the NVL function, which you can use while migrating code from Oracle without code changes.\n\nThe following example shows how Orafce NVL works for NULL input but doesn’t treat empty strings as NULL as the Oracle database does:\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```\nTherefore, if you’re using the Orafce NVL function as part of your code migration or development, you must make sure you handle empty strings for character varying or text data types, as illustrated in the following example:\n```\\npostgres=> SELECT nvl(nullif('', ''), 'A'::text) res;\\n res\\n-----\\n A\\n(1 row)\\n```\n##### **Mock NVL function as wrapper for character varying or text arguments**\nAdding the NULLIF function to each character varying or text data input in COALESCE or Orafce NVL could make the code migration process cumbersome and time-consuming. To mitigate this risk, a workaround is to create a wrapper function in PostgreSQL that parses empty strings to NULL and at the same time is performant.\n\nThe following shows a wrapper code for the NVL function with two arguments for the text type:\n```\\nCREATE OR REPLACE FUNCTION nvl(text, text)\\nRETURNS text\\nLANGUAGE sql\\nAS \$\$\\n select coalesce(NULLIF(\$1,''), NULLIF(\$2,''))\\n\$\$;\\n```\nLet’s test the NVL wrapper function we created and see if it can handle NULL as well empty strings:\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```\nBecause the empty string is possibly an input argument for the character or text data type in Oracle, you can create the preceding wrapper function to mimic similar behavior of the Oracle NVL function and expedite the code migration process.\n\nThe NVL wrapper function we created is designed for character varying or text data types only, because of empty string behavior in PostgreSQL. For other data types, you can choose between COALESCE or the Orafce NVL function.\n\n##### **Handling DECODE and CASE expressions**\nExpressions like CASE and DECODE can be part of many dynamic queries or conditional expressions, and you need to make sure it adheres to similar behavior for NULL and ” as input. See the following Oracle code:\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```\nYou get the following output:\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```\nThe PostgreSQL engine offers additional functions and extensions to mitigate empty string conditional evaluation and provides the same behavior as the Oracle engine:\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```\nYou get the following output:\n```\\nDEBUG: CASE Expression - Input as NULL, Result is NULL\\nDEBUG: CASE Expression - Input as empty string, Result is NOT NULL\\n```\nNo function like DECODE exists in PostgreSQL. You need to convert to CASE expressions manually. You can also use the Orafce DECODE function.\n\nBoth DECODE and CASE expressions treat NULL and empty strings as the same in Oracle and need proper handling when converting them to PostgreSQL.\n\nWith that understanding, let’s explore various options and best practices to migrate CASE or DECODE into PostgreSQL without breaking the functionality.\n\n##### **Option 1: Convert DECODE as a CASE expression**\nYou can convert all DECODE functions to traditional CASE expressions in PostgreSQL while migrating code from Oracle. But you must always remember the correct implementation of CASE when empty strings might appear as input to the CASE expression.\n\nThe following is the Oracle code:\n```\\nSELECT DECODE(var, null, 'NULL', 'NOT NULL') \\n FROM DUAL;\\n```\nThe following is the right approach in PostgreSQL to write a searched CASE expression:\n```\\nSELECT CASE WHEN nullif(var, '') IS null THEN 'NULL' \\n ELSE 'NOT NULL'\\n END; \\n```\n##### **Option 2: Use the Orafce DECODE function in PostgreSQL**\nLet’s test the DECODE function provided by the Orafce extension with different types of input parameters and learn how to correctly handle empty strings.\n\nWhen the input is a non-NULL string, Oracle DECODE and Orafce DECODE provide the same output.\n```\\nSQL> SELECT DECODE('one', null, 'NULL', 'NOT NULL') res FROM DUAL;\\n\\n RES\\n--------\\nNOT NULL\\n```\nThe following is the PostgreSQL code:\n```\\npostgres=> SELECT DECODE('one'::text, null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\n NOT NULL\\n(1 row)\\n```\nWhen the input is NULL, Oracle DECODE and Orafce DECODE provide the same output.\n\nThe following is the Oracle code:\n```\\nSQL> SELECT DECODE(null, null, 'NULL', 'NOT NULL') res FROM DUAL;\\n RES\\n ----\\n NULL\\n```\nThe following is the PostgreSQL code:\n```\\npostgres=> SELECT DECODE(null::text, null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\n NULL\\n (1 row)\\n```\nWhen the input is an empty string, Oracle DECODE and Orafce DECODE provide different output.\n\nThe following is the Oracle code:\n```\\nSQL> SELECT DECODE('', null, 'NULL', 'NOT NULL') res FROM DUAL;\\n RES\\n ----\\n NULL\\n```\n\nThe following is the PostgreSQL code:\n```\\npostgres=> SELECT DECODE(''::text, null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\nNOT NULL\\n(1 row)\\n```\nIn this case, you must either convert to CASE, as described earlier, or use NULLIF with DECODE:\n```\\npostgres=> SELECT DECODE(nullif('', ''), null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\n NULL\\n (1 row)\\n```\n\n#### **Conclusion**\nHandling empty strings and their evaluation with different operators, expressions, or functions like IS NULL, NVL, CASE, and DECODE should be an important consideration when migrating your database from Oracle to PostgreSQL. In this post, we have discussed the importance of handling empty strings and viable solutions during migration from Oracle to PostgreSQL. Our examples cover different variants of handling empty strings to understand the overall components, impacts, and considerations of migration to PostgreSQL.\n\nIf you have any questions or suggestions about this post, leave a comment.\n\n##### **About the Authors**\n\n\n\n**Sashikanta Pattanayak** works as a Lead Consultant with the Professional services team at AWS. He works with customers to build scalable, highly available and secure solutions in the AWS cloud. He specializes in homogeneous and heterogeneous database migrations.\n\n\n\n**Deepak Mahto**was a Consultant with the AWS Proserve Team in India. He worked as Database Migration Lead, helping and enabling customers to migrate from commercial engines to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail). His passion is automation and has designed and implemented multiple database or migration related tools.\n\n\n\n**Vinay Paladi** is a Database Consultant with the Professional Services team at Amazon Web Services. He works as a database migration specialist, helping and enabling customers to build high-available, cost-effective database solutions and migrate their commercial engines to AWS cloud. He is passionate about building innovative solutions to accelerate database journey to cloud.\n\n\n","render":"<p>An Oracle-to-PostgreSQL migration in the AWS Cloud can be a multistage process with different technologies and skills involved, starting from the assessment stage to the cutover stage. AWS offers services that make PostgreSQL database deployments easy to set up, manage, and scale for the cloud in a cost-efficient manner. These services are <a href=\\"https://aws.amazon.com/rds/postgresql/\\" target=\\"_blank\\">Amazon Relational Database Service (Amazon RDS) for PostgreSQL</a> and <a href=\\"https://aws.amazon.com/rds/aurora/details/postgresql-details/\\" target=\\"_blank\\">Amazon Aurora PostgreSQL-Compatible Edition</a>.</p>\\n<p>During the schema or code conversion phase, we can use <a href=\\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html\\" target=\\"_blank\\">AWS Schema Conversion Tool</a> (AWS SCT) to convert the majority of the code. For code conversion exceptions raised by AWS SCT, we can refer to <a href=\\"https://aws.amazon.com/prescriptive-guidance/?apg-all-cards.sort-by=item.additionalFields.sortText&amp;apg-all-cards.sort-order=desc&amp;awsf.apg-new-filter=*all&amp;awsf.apg-content-type-filter=*all&amp;awsf.apg-code-filter=*all&amp;awsf.apg-category-filter=*all&amp;awsf.apg-rtype-filter=*all&amp;awsf.apg-isv-filter=*all&amp;awsf.apg-product-filter=*all&amp;awsf.apg-env-filter=*all\\" target=\\"_blank\\">migration patterns</a> and the <a href=\\"https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook-12.4.pdf\\" target=\\"_blank\\">Oracle to PostgreSQL migration playbook</a>.</p>\\n<p>Converting code from Oracle to a PostgreSQL-compatible engine may involve multiple corner cases that are important for your overall migration success. Handling empty strings (”) with all NULL-compatible operators or expressions while migrating from Oracle to PostgreSQL is vital to achieve overall functional acceptance on the converted code and have a holistic impact.</p>\n<p>Oracle databases treat empty strings and NULL as the same. However, in PostgreSQL, empty strings and NULL are different. If you’re running workloads on Oracle, you might implement procedural code with business logic, assuming that empty strings are treated as NULL when performing any comparison or NULL check. In this post, we cover different variants of handling empty strings in PostgreSQL and viable solutions during migration. We show examples to understand the overall components, impacts, and considerations of migration.</p>\n<h4><a id=\\"NULL_vs_empty_strings_8\\"></a><strong>NULL vs. empty strings</strong></h4>\\n<p>Oracle databases treat NULL and empty strings as the same; you can use empty strings and NULL interchangeably in any operation. The following conditional statements in Oracle are all evaluated to NULL:</p>\n<pre><code class=\\"lang-\\">SQL&gt; set NULL (NULL);\\n \\nSQL&gt; SELECT length(NULL) res FROM DUAL;\\n \\n RES\\n\\t----------\\n\\t (NULL)\\n \\n \\nSQL&gt; SELECT length('') res FROM DUAL;\\n \\n RES\\n\\t----------\\n\\t (NULL)\\n</code></pre>\\n<p>However, in PostgreSQL, NULL means the value is unknown or not given, and an empty string is a string of 0 length. Therefore, NULL and empty strings are interpreted, represented, and operated differently. A PostgreSQL database treats empty strings as an identifiable value that is processed similar to other available characters:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; \\\\pset null (NULL)\\n\\npostgres=&gt; SELECT length(NULL) res;\\n\\n res\\n--------\\n (NULL)\\n(1 row)\\n\\n\\npostgres=&gt; SELECT length('') res;\\n\\n res\\n-----\\n 0\\n(1 row)\\n</code></pre>\\n<p>The following table summarizes these differences.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/c0aff7287bd441fcace5031099fae472_image.png\\" alt=\\"image.png\\" /></p>\n<p>Note the following differences between NULL and empty strings in PostgreSQL:</p>\n<ul>\\n<li>NULL can be assigned to any type, as opposed to empty strings, which aren’t compatible with date or numerical fields. See the following example code:</li>\n</ul>\\n<pre><code class=\\"lang-\\">postgres=&gt; select null::int, null::date;\\n int4 | date\\n------+------\\n |\\n(1 row)\\n\\npostgres=&gt; select ''::int;\\nERROR: invalid input syntax for type integer: &quot;&quot;\\nLINE 1: select ''::int;\\n^\\n\\npostgres=&gt; select ''::date;\\nERROR: invalid input syntax for type date: &quot;&quot;\\nLINE 1: select ''::date;\\n^\\n</code></pre>\\n<ul>\\n<li>NULL is an unknown value. It doesn’t have a value, as opposed to an empty string, which is a value but an empty one.</li>\n<li>Finding the size or length of NULL results in NULL. An empty string is a string of 0 length.</li>\n<li>NULL = NULL results in NULL, as opposed to ”=”, which evaluates to TRUE.</li>\n<li>NULL is not comparable to anything, not even to itself, whereas empty strings can be compared.</li>\n</ul>\\n<p>One of the biggest differences between Oracle and PostgreSQL is how NULL and empty strings operate with regards to composite unique indexes and constraints. PostgreSQL allows multiple rows with NULL in unique indexes. In contrast, Oracle restricts multiple NULL instances and empty strings in a composite unique constraint or unique index. When encountering an empty string, both Oracle and PostgreSQL throw an error for multiple entries.</p>\n<p>The following is an example using Oracle :</p>\n<pre><code class=\\"lang-\\">SQL&gt; CREATE TABLE test_unq(c1 integer, c2 varchar2(10));\\n\\nSQL&gt; ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);\\n\\nSQL&gt; insert into test_unq values(1, null);\\n\\n1 row created.\\n\\nSQL&gt; 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<p>Because Oracle treats empty strings as NULL, multiple entries of empty strings to unique constraints or unique indexes are violated:</p>\n<pre><code class=\\"lang-\\">SQL&gt; insert into test_unq values(2, '');\\n\\n1 row created.\\n\\nSQL&gt; 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</code></pre>\\n<p>The following is the same example using PostgreSQL:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; CREATE TABLE test_unq(c1 integer, c2 character varying(10));\\n\\npostgres=&gt; ALTER TABLE test_unq ADD CONSTRAINT test_unq_c UNIQUE(c1, c2);\\n\\npostgres=&gt; insert into test_unq values(1, null);\\nINSERT 0 1\\n\\npostgres=&gt; insert into test_unq values(1, null);\\nINSERT 0 1\\n</code></pre>\\n<p>PostgreSQL databases treat empty strings and NULL as different. All empty strings are equal, and therefore multiple entries of empty strings to a unique constraint or unique index are violated:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; insert into test_unq values(2, '');\\nINSERT 0 1\\n\\npostgres=&gt; insert into test_unq values(2, '');\\nERROR: duplicate key value violates unique constraint &quot;test_unq_c&quot;\\nDETAIL: Key (c1, c2)=(2, ) already exists.\\n</code></pre>\\n<h4><a id=\\"Handling_empty_strings_in_PostgreSQL_124\\"></a><strong>Handling empty strings in PostgreSQL</strong></h4>\\n<p>From the preceding discussion, it’s evident that when migrating from Oracle to PostgreSQL, empty string handling is important and crucial.</p>\n<p>Let’s create a sample table and insert data to it. You can use the following SQL statements to create a table and insert data in both Oracle and PostgreSQL:</p>\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</code></pre>\\n<p>In Oracle, because empty strings are treated as NULL, the preceding insert statements #2 and #3 will store NULL for column <code>tname</code> in the table. However, in PostgreSQL, the table will store NULL for the #2 and an empty string for the #3 insert statements.</p>\\n<p>You can see the difference in Oracle and PostgreSQL if rows are selected with the IS NULL or IS NOT NULL operator.</p>\n<p>The following is the Oracle code:</p>\n<pre><code class=\\"lang-\\">SQL&gt; 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</code></pre>\\n<p>The following is the PostgreSQL code:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; 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<p>If you want to select all rows that have NULL or empty strings in PostgreSQL, you must explicitly add the empty string condition in the query’s WHERE clause:</p>\n<pre><code class=\\"lang-\\"> postgres=&gt; 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<p>Optionally, you can use NULL handling functions such as COALESCE or NULLIF in PostgreSQL to deal with scenarios containing empty strings.</p>\n<h5><a id=\\"COALESCE_function_178\\"></a><strong>COALESCE function</strong></h5>\\n<p>The <a href=\\"https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL\\" target=\\"_blank\\">COALESCE</a> function returns the first of its arguments that is not NULL. NULL is returned only if all arguments are NULL. You can adjust the <code>WHERE tname IS NULL</code> filter condition to <code>WHERE coalesce(tname, '') = ''</code> to get all rows whose <code>tname</code> is either empty or not known.</p>\\n<p>However, changing WHERE clauses in SQL queries to support the functionality might affect the run plan of query. The database might not optimize the plan because the particular filter is changed, and any index created on such column wouldn’t be used. There are chances that your query could be slower. In those cases, you must analyze the query run plan, revisit the indexes created, and create <a href=\\"https://www.postgresql.org/docs/14/indexes-partial.html\\" target=\\"_blank\\">partial indexes</a> if required on the table to tune the query. Query tuning is beyond the scope of this post.</p>\\n<p>The following code is an example of the COALESCE function:</p>\n<p>PostgreSQL:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; 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>\\n<p>Oracle:</p>\n<pre><code class=\\"lang-\\">SQL&gt; 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<p>Using COALESCE to handle IS NULL or IS NOT NULL for empty strings requires a lot of code changes. A simpler way would be to use the NULLIF function.</p>\n<h5><a id=\\"NULLIF_function_206\\"></a><strong>NULLIF function</strong></h5>\\n<p><a href=\\"https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-NULLIF\\" target=\\"_blank\\">NULLIF</a> returns NULL if both the arguments are same. It returns the 1st argument when both the arguments are different. We use it to compare the input expression with the empty string and return NULL:</p>\\n<pre><code class=\\"lang-\\">postgres=&gt; \\\\pset null (NULL)\\n\\npostgres=&gt; SELECT NULLIF('', '') res;\\n res\\n--------\\n (NULL)\\n(1 row)\\n</code></pre>\\n<p>We transform native PostgreSQL function with NULLIF for cases where we might encounter empty strings (”) as input. For example, if you have a condition like <code>“tname IS NULL”</code> where <code>“tname”</code> can contain NULL, then you can change to <code>“nullif(tname, '') IS NULL” </code>in PostgreSQL.</p>\\n<pre><code class=\\"lang-\\">postgres=&gt; 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 is required as part of expression as PostgreSQL “IS NULL” operator do not handle empty string */\\n\\npostgres=&gt; 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<p>With all such cases of NULL and empty strings, let’s dive deep into other database expressions or functions that require proper attention for porting Oracle procedural code to PostgreSQL.</p>\n<h4><a id=\\"NULL_handling_functions_and_challenges_with_empty_strings_236\\"></a><strong>NULL handling functions and challenges with empty strings</strong></h4>\\n<p>NULL handling functions in both Oracle and PostgreSQL work the same in any comparison or expression. However, because an empty string is another way of representing NULL in Oracle, no difference is observed when NULL handling functions are operated with NULL or empty strings. But in a PostgreSQL database, NULL handling functions in conjunction with empty strings evaluate to FALSE. This creates a challenge when migrating from Oracle to PostgreSQL to simulate the same behavior for NULL handling functions when dealing with empty strings.</p>\n<p>The following table summarizes the overall behavior between Oracle and PostgreSQL regarding empty strings.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/13cb11de6c5643919f906404c27d4024_image.png\\" alt=\\"image.png\\" /></p>\n<p>Now that we have a better understanding of the differences between Oracle and PostgreSQL, we can walk through some of the workarounds and best practices to use during the conversion phase.</p>\n<h5><a id=\\"Handling_IS_NULL_and_IS_NOT_NULL_245\\"></a><strong>Handling IS NULL and IS NOT NULL</strong></h5>\\n<p>If you’re using conditional expressions like IS NULL and IS NOT NULL multiple times as part of procedural code in Oracle, then you must validate that they will work in PostgreSQL with respect to empty strings. Use the NULLIF function in PostgreSQL for situations where a column or variable value might become an empty string during an expression evaluation.</p>\n<p>During code migration from your Oracle to PostgreSQL database, adding NULLIF is required only for character varying data types. This is not compatible for other data types like NUMERIC or DATE.</p>\n<h5><a id=\\"Handling_NVL_and_COALESCE_250\\"></a><strong>Handling NVL and COALESCE</strong></h5>\\n<p>PostgreSQL databases don’t have a built-in NVL function, and instead support the COALESCE function, which is ANSII compliant for NVL. The COALESCE function works perfectly fine for data types other than strings. This is because strings can accept empty strings. For situations that might accept empty strings as input, you must handle them carefully with the PostgreSQL COALESCE function.</p>\n<p>The following is the Oracle code:</p>\n<pre><code class=\\"lang-\\">SQL&gt; SELECT coalesce(NULL, 'A') res FROM DUAL;\\n\\n RES\\n----------\\n A\\n</code></pre>\\n<p>The following is the PostgreSQL code:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT coalesce(NULL, 'A') res;\\n res\\n -----\\n A\\n(1 row) \\n</code></pre>\\n<p>The following example shows how the functionality of COALESCE differs with empty strings as input.</p>\n<p>The following is the Oracle code:</p>\n<pre><code class=\\"lang-\\">SQL&gt; SELECT coalesce('', 'A') res FROM DUAL;\\n\\n RES\\n----------\\n A\\n</code></pre>\\n<p>The following is the PostgreSQL code:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT coalesce('', 'A') res;\\n res\\n -----\\n\\n (1 row)\\n</code></pre>\\n<p>Although the behavior of COALESCE in PostgreSQL conforms to the standard, it’s not always compatible based on input or variable values while migrating from Oracle. Let’s understand the workaround for COALESCE for empty string handling. The following is the PostgreSQL code:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT coalesce(nullif('', ''), 'a') res;\\n res\\n -----\\n a\\n(1 row)\\n</code></pre>\\n<h5><a id=\\"Orafce_NVL_function_295\\"></a><strong>Orafce NVL function</strong></h5>\\n<p>Orafce is an extension supported in <a href=\\"https://aws.amazon.com/rds/postgresql/\\" target=\\"_blank\\">Amazon Relational Database Service (Amazon RDS) for PostgreSQL</a> and <a href=\\"https://aws.amazon.com/rds/aurora/postgresql-features/\\" target=\\"_blank\\">Amazon Aurora PostgreSQL-Compatible Edition</a>. When you create this extension in PostgreSQL, it brings modules containing some useful functions that can help with porting your Oracle application to PostgreSQL. Orafce provides the NVL function, which you can use while migrating code from Oracle without code changes.</p>\\n<p>The following example shows how Orafce NVL works for NULL input but doesn’t treat empty strings as NULL as the Oracle database does:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT nvl(null, 'A'::text) res;\\n res\\n-----\\n A\\n(1 row)\\n\\npostgres=&gt; SELECT nvl('', 'A'::text) res;\\n res\\n-----\\n\\n(1 row)\\n</code></pre>\\n<p>Therefore, if you’re using the Orafce NVL function as part of your code migration or development, you must make sure you handle empty strings for character varying or text data types, as illustrated in the following example:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT nvl(nullif('', ''), 'A'::text) res;\\n res\\n-----\\n A\\n(1 row)\\n</code></pre>\\n<h5><a id=\\"Mock_NVL_function_as_wrapper_for_character_varying_or_text_arguments_320\\"></a><strong>Mock NVL function as wrapper for character varying or text arguments</strong></h5>\\n<p>Adding the NULLIF function to each character varying or text data input in COALESCE or Orafce NVL could make the code migration process cumbersome and time-consuming. To mitigate this risk, a workaround is to create a wrapper function in PostgreSQL that parses empty strings to NULL and at the same time is performant.</p>\n<p>The following shows a wrapper code for the NVL function with two arguments for the text type:</p>\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<p>Let’s test the NVL wrapper function we created and see if it can handle NULL as well empty strings:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT coalesce('', 'A') res;\\n res\\n -----\\n\\n(1 row)\\n\\n\\npostgres=&gt; SELECT nvl(NULL, 'A') res;\\n res\\n -----\\n A\\n(1 row)\\n\\n\\npostgres=&gt; SELECT nvl('', 'A') res;\\n res\\n -----\\n A\\n(1 row)\\n</code></pre>\\n<p>Because the empty string is possibly an input argument for the character or text data type in Oracle, you can create the preceding wrapper function to mimic similar behavior of the Oracle NVL function and expedite the code migration process.</p>\n<p>The NVL wrapper function we created is designed for character varying or text data types only, because of empty string behavior in PostgreSQL. For other data types, you can choose between COALESCE or the Orafce NVL function.</p>\n<h5><a id=\\"Handling_DECODE_and_CASE_expressions_358\\"></a><strong>Handling DECODE and CASE expressions</strong></h5>\\n<p>Expressions like CASE and DECODE can be part of many dynamic queries or conditional expressions, and you need to make sure it adheres to similar behavior for NULL and ” as input. See the following Oracle code:</p>\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</code></pre>\\n<p>You get the following output:</p>\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>\\n<p>The PostgreSQL engine offers additional functions and extensions to mitigate empty string conditional evaluation and provides the same behavior as the Oracle engine:</p>\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<p>You get the following output:</p>\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>\\n<p>No function like DECODE exists in PostgreSQL. You need to convert to CASE expressions manually. You can also use the Orafce DECODE function.</p>\n<p>Both DECODE and CASE expressions treat NULL and empty strings as the same in Oracle and need proper handling when converting them to PostgreSQL.</p>\n<p>With that understanding, let’s explore various options and best practices to migrate CASE or DECODE into PostgreSQL without breaking the functionality.</p>\n<h5><a id=\\"Option_1_Convert_DECODE_as_a_CASE_expression_451\\"></a><strong>Option 1: Convert DECODE as a CASE expression</strong></h5>\\n<p>You can convert all DECODE functions to traditional CASE expressions in PostgreSQL while migrating code from Oracle. But you must always remember the correct implementation of CASE when empty strings might appear as input to the CASE expression.</p>\n<p>The following is the Oracle code:</p>\n<pre><code class=\\"lang-\\">SELECT DECODE(var, null, 'NULL', 'NOT NULL') \\n FROM DUAL;\\n</code></pre>\\n<p>The following is the right approach in PostgreSQL to write a searched CASE expression:</p>\n<pre><code class=\\"lang-\\">SELECT CASE WHEN nullif(var, '') IS null THEN 'NULL' \\n ELSE 'NOT NULL'\\n END; \\n</code></pre>\\n<h5><a id=\\"Option_2_Use_the_Orafce_DECODE_function_in_PostgreSQL_465\\"></a><strong>Option 2: Use the Orafce DECODE function in PostgreSQL</strong></h5>\\n<p>Let’s test the DECODE function provided by the Orafce extension with different types of input parameters and learn how to correctly handle empty strings.</p>\n<p>When the input is a non-NULL string, Oracle DECODE and Orafce DECODE provide the same output.</p>\n<pre><code class=\\"lang-\\">SQL&gt; SELECT DECODE('one', null, 'NULL', 'NOT NULL') res FROM DUAL;\\n\\n RES\\n--------\\nNOT NULL\\n</code></pre>\\n<p>The following is the PostgreSQL code:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT DECODE('one'::text, null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\n NOT NULL\\n(1 row)\\n</code></pre>\\n<p>When the input is NULL, Oracle DECODE and Orafce DECODE provide the same output.</p>\n<p>The following is the Oracle code:</p>\n<pre><code class=\\"lang-\\">SQL&gt; SELECT DECODE(null, null, 'NULL', 'NOT NULL') res FROM DUAL;\\n RES\\n ----\\n NULL\\n</code></pre>\\n<p>The following is the PostgreSQL code:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT DECODE(null::text, null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\n NULL\\n (1 row)\\n</code></pre>\\n<p>When the input is an empty string, Oracle DECODE and Orafce DECODE provide different output.</p>\n<p>The following is the Oracle code:</p>\n<pre><code class=\\"lang-\\">SQL&gt; SELECT DECODE('', null, 'NULL', 'NOT NULL') res FROM DUAL;\\n RES\\n ----\\n NULL\\n</code></pre>\\n<p>The following is the PostgreSQL code:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT DECODE(''::text, null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\nNOT NULL\\n(1 row)\\n</code></pre>\\n<p>In this case, you must either convert to CASE, as described earlier, or use NULLIF with DECODE:</p>\n<pre><code class=\\"lang-\\">postgres=&gt; SELECT DECODE(nullif('', ''), null, 'NULL', 'NOT NULL') res;\\n res\\n----------\\n NULL\\n (1 row)\\n</code></pre>\\n<h4><a id=\\"Conclusion_528\\"></a><strong>Conclusion</strong></h4>\\n<p>Handling empty strings and their evaluation with different operators, expressions, or functions like IS NULL, NVL, CASE, and DECODE should be an important consideration when migrating your database from Oracle to PostgreSQL. In this post, we have discussed the importance of handling empty strings and viable solutions during migration from Oracle to PostgreSQL. Our examples cover different variants of handling empty strings to understand the overall components, impacts, and considerations of migration to PostgreSQL.</p>\n<p>If you have any questions or suggestions about this post, leave a comment.</p>\n<h5><a id=\\"About_the_Authors_533\\"></a><strong>About the Authors</strong></h5>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/fc1bb77d7b4243198cd9cbcfbfec8267_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Sashikanta Pattanayak</strong> works as a Lead Consultant with the Professional services team at AWS. He works with customers to build scalable, highly available and secure solutions in the AWS cloud. He specializes in homogeneous and heterogeneous database migrations.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/ebcae5ff3f2e4b4ba96fd92f96891944_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Deepak Mahto</strong>was a Consultant with the AWS Proserve Team in India. He worked as Database Migration Lead, helping and enabling customers to migrate from commercial engines to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail). His passion is automation and has designed and implemented multiple database or migration related tools.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/53c877c054e04dfd8254b2f7174910a3_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Vinay Paladi</strong> is a Database Consultant with the Professional Services team at Amazon Web Services. He works as a database migration specialist, helping and enabling customers to build high-available, cost-effective database solutions and migrate their commercial engines to AWS cloud. He is passionate about building innovative solutions to accelerate database journey to cloud.</p>\n"}