SUMMARY: This article discusses the differences between how Oracle and PostgreSQL evaluate NULL characters and empty strings. Oracle reads empty strings as NULLs, while PostgreSQL treats them as empty. Concatenating NULL values with non-NULL characters results in that character in Oracle, but NULL in PostgreSQL.
Oracle and PostgreSQL behave similarly in many cases, but one way they differ is in their treatment of NULLs and empty strings.
NULLs and empty strings in Oracle
In Oracle, NULLs and empty strings are equivalent when it comes to values stored in the database. We'll demonstrate this behaviour with a simple table and some data:
CREATE TABLE test (
id numeric(3,0) PRIMARY KEY,
content varchar(255)
);
INSERT INTO test (id, content) VALUES (1, NULL);
INSERT INTO test (id, content) VALUES (2, '');
INSERT INTO test (id, content) VALUES (3, ' ');
INSERT INTO test (id, content) VALUES (4, 'x');
Here we have a value that's explicitly NULL, an empty string, a string with a single space in it, and another with a 1-character string. Now let's test this data:
Note: Remember to change the default null output from '' to (null)
psql -P 'null=(null)'
SELECT
id,
content,
CASE WHEN content IS NULL THEN 1 ELSE 0 END AS isnull,
CASE WHEN content = '' THEN 1 ELSE 0 END AS isempty,
CASE WHEN content = ' ' THEN 1 ELSE 0 END AS blank
FROM
test;
| ID | CONTENT | ISNULL | ISEMPTY | BLANK |
|----|---------|--------|---------|-------|
| 1 | (null) | 1 | 0 | 0 |
| 2 | (null) | 1 | 0 | 0 |
| 3 | | 0 | 0 | 1 |
| 4 | x | 0 | 0 | 0 |
This tells us that the empty string was treated as a NULL when inserted into the table, and that it can't be compared to regular values as if it were an empty string because it's a full-fledged NULL. So, empty strings cannot be stored in the database.
However, if we have a single space, this isn't converted, as it isn't an empty string. The same goes for when we have any non-whitespace characters; it's all the same.
NULLs and empty strings in PostgreSQL
But in PostgreSQL, the story is different. Let's do the same thing again, but in PostgreSQL this time. We don't need to change anything about the above DDL, DML or SQL, so let's just look at the results we end up with:
| id | content | isnull | isempty | blank |
|----|---------|--------|---------|-------|
| 1 | (null) | 1 | 0 | 0 |
| 2 | | 0 | 1 | 0 |
| 3 | | 0 | 0 | 1 |
| 4 | x | 0 | 0 | 0 |
We can ignore the bottom two rows because the functionality is the same, as expected. If we look at the first two, the NULL we inserted is still considered a NULL and can't be compared to an empty string. But when we look at the empty string that we inserted for the 2nd row, we don't have a NULL value, we still have an empty string. No conversion has occurred, and we can see that it isn't considered to be a NULL in the query results, but an empty string. PostgreSQL's behaviour follows the standard in its treatment of NULL values.
NULLs and non-NULLs
Another important difference between Oracle and PostgreSQL is when a NULL value is concatenated with a non-NULL character. Let's see what we get if we try concatenating a NULL or a 1-character string to the values in our example table.
The query we will be using is:
SELECT id, content,
content || NULL AS concatnull,
content || 'x' AS concatchar
FROM test;
Oracle:
| ID | CONTENT | CONCATNULL | CONCATCHAR |
|----|---------|------------|------------|
| 1 | (null) | (null) | x |
| 2 | (null) | (null) | x |
| 3 | | | x |
| 4 | x | x | xx |
PostgreSQL:
| id | content | concatnull | concatchar |
|----|---------|------------|------------|
| 1 | (null) | (null) | (null) |
| 2 | | (null) | x |
| 3 | | (null) | x |
| 4 | x | (null) | xx |
What is interesting here is that, in Oracle, concatenating a NULL and a character together results in the character as the output value, whereas in PostgreSQL, the fact that a NULL is present in either value means that the result is NULL as the output value, no matter what it is being concatenated with.
In EDB Postgres Advanced Server, there is a compatibility setting called edb_redwood_strings, which, when set to true, enables the same behaviour as Oracle when it comes to concatenation.