When migrating from Oracle to PostgreSQL, one of the most important differences to consider is the treatment of null and empty string values. In Oracle, null and empty string are treated as the same entity, but in PostgreSQL, they are distinct. Failing to account for this distinction during migration can lead to logical errors and unexpected behavior in the database. This post will explore key nuances and demonstrate how to handle these differences effectively.

Comparing null and empty string

In Oracle, an empty string is treated as null when compared. Consider the following query:

SELECT CASE WHEN '' IS NULL THEN 'is null' ELSE 'is not null' END AS example_1,
CASE WHEN ''='' THEN 'is equal' ELSE 'is not equal' END AS example_2
FROM dual;

The result is following:

EXAMPLE_1 EXAMPLE_2
is null is not equal

Here, Oracle treats ” (empty string) as null. However, in PostgreSQL, the same logic doesn’t apply:

SELECT CASE WHEN '' IS NULL THEN 'is null' ELSE 'is not null' END AS example_1,
CASE WHEN ''='' THEN 'is equal' ELSE 'is not equal' END AS example_2,
CASE WHEN NULL IS NOT DISTINCT FROM NULL THEN 'is equal' ELSE 'is not equal' END AS example_3;

The result is following:

EXAMPLE_1 EXAMPLE_2 EXAMPLE_3
is not null is equal is equal

In PostgreSQL, an empty string is distinct from null, which means that the first comparison returns is not null. Also, in Oracle, an empty string (”) is treated as null, so the comparison using “=” works differently. Note that PostgreSQL provides a useful feature: the “is not distinct from” operator, which allows nulls to be compared as if they were regular values. This simplifies expressions like:

(field1 = field2 OR (field1 IS NULL AND field2 IS NULL))

by using:

field1 IS NOT DISTINCT FROM field2

Function DECODE in Oracle vs PostgreSQL

The Oracle DECODE function allows comparisons that treat nulls and empty strings as equivalent, which isn’t directly supported in PostgreSQL. For example, the following Oracle query works as expected:

SELECT decode(NULL, NULL, 'is null', '1', '1', 'default') AS example_1,
decode(NULL, '', 'is empty string', '1', '1', 'default') AS example_2
FROM dual;
EXAMPLE_1 EXAMPLE_2
is null is empty string

When using SCT to convert this to PostgreSQL, the results are incorrect because PostgreSQL lacks native support for DECODE. Here’s an example of an AWS SCT-generated query:

SELECT CASE NULL WHEN NULL THEN 'is null' ELSE 'default' END AS example_1,
CASE NULL WHEN '' THEN 'is empty string' ELSE 'default' END AS example_2;
EXAMPLE_1 EXAMPLE_2
default default

This must be rewritten in PostgreSQL to handle null and empty string distinctions correctly:

SELECT CASE WHEN NULL IS NULL THEN 'is null' ELSE 'default' END AS example_1,
CASE WHEN NULL='' THEN 'is empty string' ELSE 'default' END AS example_2;
EXAMPLE_1 EXAMPLE_2
is null default

Handling numeric, date, and interval types

The situation becomes even more complex with numeric, date, or interval types in Oracle. When comparing values, Oracle’s DECODE allows empty strings to be treated like nulls. Here’s a sample query in Oracle:

SELECT decode('', NULL, 'is null', 1, '1', 'default') AS example_1

FROM dual;
EXAMPLE_1
is null

In PostgreSQL, after AWS SCT automatic conversion, errors can occur, especially when empty strings are involved:

SELECT CASE '' WHEN NULL THEN 'is null' WHEN 1 THEN '1' ELSE 'default' END AS example_1;

This query throws error and needs to be corrected by explicitly handling empty strings as null:

SELECT CASE WHEN NULL IS NULL THEN 'is null' WHEN NULL = 1 THEN '1' ELSE 'default' END AS example_1;
EXAMPLE_1
is null

Concatenation differences

In Oracle, concatenation with || behaves in a way that ignores null values unless all concatenated strings are null. However, in PostgreSQL, concatenation with || will return null if any of the values being concatenated are null. SCT often converts concatenation using the concat_ws function in PostgreSQL:

concat_ws('', string_1, string_2, ... string_N)

This function behaves similarly to Oracle’s ||. But it returns an empty string if all inputs are null, while Oracle operator || returns null in this case. For cases where you expect null behavior, you’ll need to adjust your conditions:

IF concat_ws('', string_1, string_2) = '' THEN ...

Or, a more precise approach:

IF string_1 IS NULL AND string_2 IS NULL THEN ...

Empty string and NOT NULL constraint

In Oracle, inserting an empty string into a NOT NULL column will trigger a rejection because it’s treated as null. In PostgreSQL, however, the insert will succeed because empty strings are distinct from null. To ensure the same behavior, you can add a check constraint:

CREATE TABLE some_table (
field_with_not_null_constraint text NOT NULL,
CHECK (field_with_not_null_constraint <> ''::text)
);

Conclusion

When migrating from Oracle to PostgreSQL, understanding the differences between null and empty string handling is crucial. Oracle treats null and empty string as the same entity, while PostgreSQL distinguishes between them, which can lead to unexpected outcomes if not handled properly. By making use of PostgreSQL’s is not distinct from operator, adjusting comparisons, and carefully handling concatenation and constraints, you can ensure a smoother migration process and avoid common pitfalls.

If you’re planning a migration or dealing with issues related to null and empty string behavior, ensuring that your code is correctly adapted for PostgreSQL is key to maintaining data integrity and performance.

Questions? Contact us