Thread: '' <> NULL

On Mon, Mar 24, 2003 at 08:45:00PM -0300, Cristian Custodio wrote:

> 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?

It's been discussed before, and I've never seen a way to do it.

It's important to see that, even though Oracle does it that way, ''
is _not_ NULL.  It's an empty string.  There's a fairly important
difference.

Could you arrange things such that you always look for and insert ''?
Then you'd have empty values all the time.  (NOT NULL constraints
wouldn't work against these cases, of course, because they're not
null.)

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

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.

On Tue, 25 Mar 2003, scott.marlowe wrote:

> > 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

transform_null_equals allows =NULL to be treated as IS NULL. It
won't help the empty string <-> NULL comparisons AFAICT.

scott.marlowe writes:

> It is transform_null_equals.  Set it to true, shut down and restart your
> database, and
>
> 'select 1 where '' = NULL' should work.

No, transform_null_equals has nothing to do with this.

--
Peter Eisentraut   peter_e@gmx.net

On Tue, 25 Mar 2003, Peter Eisentraut wrote:

> scott.marlowe writes:
>
> > It is transform_null_equals.  Set it to true, shut down and restart your
> > database, and
> >
> > 'select 1 where '' = NULL' should work.
>
> No, transform_null_equals has nothing to do with this.

Darn, sorry for the wrong info.