'' <> NULL
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
POSTGRE
select 1 where '' i snull
Result: NULL
select 1 where trim(' ') is null
result: NULL
insert into tabela values ('')
result: insere '' in table
We can't change all ours source code
bacause there are very occurrences.
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?
Cristian
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