Null and Empty String in Oracle vs SQL Server vs PostgreSQL

When you are planning database migrations to PostgreSQL, it is usually the small things that cause the biggest production bugs. One of the most common traps for developers is how different databases handle NULL and empty strings ('').

While they might seem like similar concepts, representing the absence of a value, the way a database engine interprets them can change your query results, break your unique constraints, or cause data loads to fail. In this guide, we will compare the behavior of Oracle, SQL Server, and PostgreSQL to help you avoid common migration pitfalls.

🔹 HexaRocket Insight
By the way, HexaRocket automatically handles all these scenarios, when using HexaRocket to convert Schema, migrate data and also during CDC for replication from Oracle to PostgreSQL. HexaRocket is an end-to-end database migration tool created by the creators of Ora2Pg. See differences between Ora2Pg vs HexaRocket here.

1. The Fundamentals: What is a NULL?

According to the ANSI SQL-92 standard, a NULL is a marker indicating that data is missing or unknown. It is not supposed to be a zero or an empty string. However, implementation varies significantly.

Core Storage and Behavior

Feature	Oracle	SQL Server	PostgreSQL
Empty String ('')	Treated as NULL	Treated as a distinct value	Treated as a distinct value
Storage for NULL	1 byte	0 bytes (tracked in row header)	0 bytes (tracked in null bitmap)
Length of ''	NULL	0	0

In Oracle, an empty string is effectively an alias for NULL. In PostgreSQL and SQL Server, an empty string is a valid string with a length of zero, while NULL means the value is unknown.

2. Logical Comparisons and Truth Tables

The way these databases handle logic in WHERE clauses is a major point of confusion. Because Oracle treats '' as NULL, it uses "Three-Valued Logic" (True, False, Unknown) for both.

Comparing with NULL

In standard SQL, comparisons with NULL result in UNKNOWN. It is not TRUE or FALSE. This is why you must use IS NULL or IS NOT NULL.

Condition	Value of A	Oracle	SQL Server	PostgreSQL
a IS NULL	10	FALSE	FALSE	FALSE
a IS NULL	NULL	TRUE	TRUE	TRUE
a = NULL	NULL	UNKNOWN	UNKNOWN	UNKNOWN
a != NULL	NULL	UNKNOWN	UNKNOWN	UNKNOWN
a = 10	NULL	UNKNOWN	UNKNOWN	UNKNOWN
a != 10	NULL	UNKNOWN	UNKNOWN	UNKNOWN
a > 5	NULL	UNKNOWN	UNKNOWN	UNKNOWN

Comparing with Empty String ('')

This is where the behavior diverges during a migration to PostgreSQL.

Condition	Value of A	Oracle Result	SQL Server Result	PostgreSQL Result
a = ''	'' (Empty)	UNKNOWN	TRUE	TRUE
a = ''	NULL	UNKNOWN	FALSE	FALSE
a IS NULL	'' (Empty)	TRUE	FALSE	FALSE
a IS NOT NULL	'' (Empty)	FALSE	TRUE	TRUE
LENGTH(a)	'' (Empty)	NULL	0	0

3. Comparison and Arithmetic Operators

A common mistake is assuming that expression = NULL will return true if the expression is null. As shown above, this is incorrect. You must use the IS operator.

Arithmetic with NULL

Whenever you use arithmetic operators (+, -, *, /) with NULL, the result is always NULL, regardless of the other operand.

• 10 + NULL = NULL
• 5 * NULL = NULL

This applies to all three databases. If your application logic performs calculations on columns that might contain NULL, ensure you use functions like COALESCE() or NVL() to provide a default value (like 0) before the calculation.

4. Sorting Behavior (ORDER BY)

How NULL values and empty strings are positioned in a result set differs significantly between these engines. This is particularly important for Oracle developers to understand, as Oracle groups them together while the others do not.

Database	NULL Placement (ASC)	NULL Placement (DESC)	Empty String ('') Placement
Oracle	Last	First	Treated as NULL (Last in ASC)
SQL Server	First	Last	Sorted as the "lowest" character value
PostgreSQL	Last	First	Sorted as the "lowest" character value

The Sorting "Gotcha"

In Oracle, because an empty string is a NULL, they always sort together.

In PostgreSQL and SQL Server, the empty string is a literal value. In an ASC sort, the empty string will usually appear at the very beginning of your data (before strings starting with 'A' or '1'). However, the NULL values will be placed elsewhere based on the default behavior (First in SQL Server, Last in PostgreSQL).

If you are migrating to PostgreSQL and want to maintain a specific order, you can use the NULLS FIRST or NULLS LAST modifiers in your query:

SELECT * FROM my_table ORDER BY my_column ASC NULLS FIRST;

5. String Concatenation

The behavior of combining strings differs when a NULL is involved:

• Oracle: 'Hello' || NULL results in 'Hello'. Oracle treats the NULL as an empty string.
• PostgreSQL & SQL Server: 'Hello' || NULL results in NULL( we need to use + operator in SQL Server). In standard SQL, "Unknown" combined with anything remains "Unknown."

Pro-tip: To get consistent behavior across all three, use the CONCAT() function. CONCAT('Hello', NULL) returns 'Hello' in all three databases.

6. Constraints: NOT NULL and UNIQUE

NOT NULL Constraints

In Oracle, you cannot store an empty string in a NOT NULL column. Oracle will throw a "NULL constraint violation" error. In PostgreSQL and SQL Server, an empty string is a valid value and is allowed in a NOT NULL column.

Single Column Unique Key

• Oracle: Allows unlimited NULL entries (and unlimited empty strings).
• SQL Server: By default, allows only one NULL.
• PostgreSQL: Historically allows unlimited NULLs, but only one empty string. As of PostgreSQL 15, you can use UNIQUE NULLS NOT DISTINCT to force the SQL Server behavior (only one NULL).

Composite Unique Keys (Multiple Columns)

Assume a unique constraint on (col1, col2, col3):

• Oracle: You can have multiple rows of (NULL, NULL, NULL). However, a partial match like ('A', 'B', NULL) is only allowed once.
• SQL Server: Only allows one row of (NULL, NULL, NULL) and only one row of ('A', 'B', NULL).
• PostgreSQL: Allows unlimited rows of both (NULL, NULL, NULL) and ('A', 'B', NULL) by default.

7. PostgreSQL Data Loading: The COPY Command

When performing a database migration to PostgreSQL, you will likely use the COPY command. It has specific rules for identifying NULLs:

1. Text Format: Uses \N by default to represent NULL. An empty field is an empty string.
2. CSV Format: An unquoted empty value is treated as NULL. Quoted empty values "" are treated as an empty string.

You can customize this during the load:

-- Treat the word 'N/A' as a NULL during import
COPY my_table FROM 'data.csv' WITH (FORMAT csv, NULL 'N/A');

Conclusion

Understanding these differences is mandatory for any developer involved in migrations.

• If you are moving from Oracle to PostgreSQL, your application logic might break if it expects '' to be treated as NULL.
• If you are moving from SQL Server to PostgreSQL, your unique constraints might suddenly allow "duplicate" NULL rows that were previously restricted.

🔹 HexaRocket Recommendation

To effectively handle these differences and ensure a seamless migration between databases, we recommend using HexaRocket to automate and manage your migration process with precision. With the help of HexaRocket and the experts at HexaCluster, we can test these edge cases early in your database migration to PostgreSQL, ensuring that your data integrity remains solid and your application behaves predictably across different database platforms.

Subscribe to our Newsletters and Stay tuned for more interesting topics.

If you need expert support migrating legacy or complex Oracle, SQL Server, Sybase, MySQL, MariaDB databases to PostgreSQL or distributed databases, we’re here to help. HexaCluster provides end-to-end migration and modernization services, including application migration and modernization, database migration, and PostgreSQL consulting such as performance tuning, health audits, managed DBA services, and 24/7/265 support. To start a conversation or explore how we can support your migration journey, please contact us at connect@hexacluster.ai