In PostgreSQL and Oracle, when are NULL and empty string treated the same?

Question

I created the following dbfiddle, in which you can see that field <> '' rules out the case where field is null.

https://www.db-fiddle.com/f/xdfayYMvdRZDRRASvGFdpT/0

CREATE TABLE test (
  prd_typ_cd bpchar,
  prd_prnt_typ_cd bpchar,
  prd_chld_typ_cd bpchar
);
INSERT INTO test VALUES ('SHC', null, 'DIV');

INSERT INTO test VALUES ('DIV', 'SHC', 'DEP');

INSERT INTO test VALUES ('DEP', 'DIV', null);

SELECT * FROM test WHERE prd_chld_typ_cd <> '';

I was surprised to see this, since in most languages NULL and '' (empty string) are treated differently.

The above example is from a PostgreSQL database. Would the same be true on an Oracle database? Under what circumstances do PostgreSQL and Oracle treated NULL and '' (empty string) as the same thing?

Never. You may be misled by some software (database browsers) which show nulls as empty strings. — klin
– klin, Commented Sep 29, 2018 at 1:55

Gordon Linoff · Accepted Answer · 2018-09-29 02:14:16Z

5

NULL and '' ARE being treated differently in Postgres.

 prd_chld_typ_cd <> ''

returns NULL when prd_chld_typ_cd is NULL. This is typical behavior for most operations involving NULL. You will readily find that:

 prd_chld_typ_cd = ''

behaves exactly the same when that column is NULL.

For any other value, the expression returns true or false. You would readily see this if you returned the value:

SELECT t.*, prd_chld_typ_cd <> ''
FROM test t;

What is happening is that NULL and false are treated the same way in a WHERE filter -- both filter out the row.

By contrast, NULL and '' are synonyms in Oracle. But it turns out that

 prd_chld_typ_cd <> ''
 prd_chld_typ_cd = ''

Both behave the same way when prd_chld_typ_cd is NULL. Alas, though, in Oracle, both always return NULL -- because '' is equivalent to NULL.

edited Sep 29, 2018 at 2:14

answered Sep 29, 2018 at 1:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Carlo Sirna · Accepted Answer · 2022-09-27 15:08:17Z

I don't know about postgres, but oracle has some quirks about empty strings you need to be aware about if you are planning to support it in your applications:

in Oracle the empty string is ALWAYS considered NULL: if you insert an empty string, you will read back a NULL. In oracle the empty string simply does not exist: it is just "syntax sugar" if you can write '' instead of NULL, when dealing with varchar values.
In Oracle the expression ('' is null) will evaluate to TRUE. the empty string is DE FACTO null
In oracle any comparison involving a NULL value will ALWAYS evaluate to FALSE. This means that the only operators you can apply to NULL values and that can return TRUE are "is null" and "is not null"

The consequence of the above is that, in oracle, ALL the following expressions will evaluate to FALSE, regardless of the actual value of myvar:

 myvar = '' 
 myvar <> '' 
 myvar = null
 myvar <> null

even these evaluate always to false

 null = null
 null <> null
 null = '' 
 null <> ''

so the following update will not update anything:

 update mytab set myfield ='X' where myfield = ''

as I said, you can only use the 'is null' and 'is not null' operators. so you must write it as

update mytab set myfield='X' where myfield is null.

P. S. the only oracle function that treats null as a distinct comparable value is Decode(). probably this was born as a bug, but today there is too much software that relies on this behaviour to fix it.

  decode( <expression>,
          <caseval 1>,  < exitval 1>,
          <caseval 2>,  < exitval 2>,
          .... 
           <elseval>)

If replace the "decode" with the equivalent sql standard "case" construct, it won't work: oracle didn't replicate the decode bug when implementing the case syntax

Collectives™ on Stack Overflow

In PostgreSQL and Oracle, when are NULL and empty string treated the same?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related