Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question

Question

From
Robert Partyka
Date:
Hi,

I have question:

why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is null
but
returns true on records where foofield is '' (empty string)

regards
Robert

Re: Question

From
Gaetano Mendola
Date:
Robert Partyka wrote:

> Hi,
>
> I have question:
>
> why such condition:
> foofield not like '%bar%'
>
> where foofield is varchar
>
> returns false (or rather even ignore row) on record where foofield is null
> but
> returns true on records where foofield is '' (empty string)

SQL specifications.

Empty string and NULL are two different thinks.

Regards
Gaetano Mendola

Re: Question

From
Stephan Szabo
Date:
On Mon, 13 Oct 2003, Robert Partyka wrote:

> why such condition:
> foofield not like '%bar%'
>
> where foofield is varchar
>
> returns false (or rather even ignore row) on record where foofield is
> null

Actually, it probably returns unknown(NULL) on such records.
NULL LIKE '%bar%' is unknown, so
NULL NOT LIKE '%bar%' is also unknown.

This is because NULL isn't the same as empty string, nor is it the absence
of a value, but it's an unknown value.

Re: Question

From
Date:
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

> On Mon, 13 Oct 2003, Robert Partyka wrote:
>
>> why such condition:
>> foofield not like '%bar%'
>>
>> where foofield is varchar
>>
>> returns false (or rather even ignore row) on record where foofield is
>> null
>
> Actually, it probably returns unknown(NULL) on such records.
> NULL LIKE '%bar%' is unknown, so
> NULL NOT LIKE '%bar%' is also unknown.
>
> This is because NULL isn't the same as empty string, nor is it the
> absence of a value, but it's an unknown value.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

Re: Question

From
Gaetano Mendola
Date:
vhikida@inreach.com wrote:

> If you are experienced in Oracle, this might be confusing since Oracle
> treats empty string and NULL as being the same.

Really? I don't believe it.

Regards
Gaetano Mendola

Re: Question

From
Mike Mascari
Date:
Gaetano Mendola wrote:

> vhikida@inreach.com wrote:
>
>> If you are experienced in Oracle, this might be confusing since Oracle
>> treats empty string and NULL as being the same.
>
>
> Really? I don't believe it.

It is insane, but true.

Mike Mascari
mascarm@mascari.com

Re: Question

From
Greg Stark
Date:
Mike Mascari <mascarm@mascari.com> writes:

> Gaetano Mendola wrote:
>
> > vhikida@inreach.com wrote:
> >
> >> If you are experienced in Oracle, this might be confusing since Oracle
> >> treats empty string and NULL as being the same.
> >
> >
> > Really? I don't believe it.
>
> It is insane, but true.

Uh, yeah, but I think that results in the same behaviour for the case at hand.

ie on oracle this is still holds:

 NULL LIKE '%foo%' => NULL

The idiocy is that Oracle does this:

 '' LIKE '%foo%' => NULL

because it treats '' as if you had NULL, ie, equivalent to the example above.

--
greg