Re: '' <> NULL

On Mon, 24 Mar 2003, Cristian Custodio wrote:

> Hi,
>
> I work with Oracle and to it,
> a empty quote concatenation is equal NULL.
>
> ORACLE:
> select 1 from dual where '' is null
> Result: 1
> select 1 from dual where trim('  ') is null
> result: 1
> insert into tabela values ('')
> result: insert a value null in a table

Yes, Oracle is wrong here.  They made a mistake a long time ago, and since
everyone started coding as though '' = NULL, they couldn't change it.
Maybe someday they will deprecate this behaviour then finally code it out
of their database, but I kinda doubt that.

> POSTGRE
> select 1 where '' i snull
> Result: NULL
> select 1 where trim('  ') is null
> result: NULL
> insert into tabela values ('')
> result: insere '' in table

Yes, because NULL != NULL != '' != 0 because NULL is unknown, so in set
theory it can't be assumed to be equal to anything, even itself.

> We can't change all ours source code
> bacause there are very occurrences.

Wow.  You should probably review all your code if folks were treating ''
like NULL then they've probably made some other serious mistakes in how
they treat data.  I'd definitely check your data constraints, NOT NULL
stuff, things like that.

> Does anybody here in this forum, and that work
> with Oracle already resolve this?
> Are there any way to change the PG to equal
>  '' like NULL?

I don't work with Oracle, but there is a hack in the postgresql.conf file
that lets you revert this behaviour.

It is transform_null_equals.  Set it to true, shut down and restart your
database, and

'select 1 where '' = NULL' should work.