Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?

On Fri, Jun 3, 2016 at 12:05 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 06/03/2016 11:56 AM, David W Noon wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On Fri, 3 Jun 2016 11:16:33 -0700, Michael Moore
>> (michaeljmoore(at)gmail(dot)com) wrote about "[SQL] NOT NULL CHECK (mycol
>> !='') :good idea? bad idea?" (in
>> <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n7A(at)mail(dot)gmail(dot)com>):
>>
>> In Oracle, a NOT NULL constraint on a table column of VARCHAR in
>>> essence says: "You need to put at least 1 character for a value".
>>> There is no such thing as a zero-length string in Oracle, it's
>>> either NULL or it has some characters.
>>>
>>
>> So Oracle is not compliant with ANSI standard SQL.
>>
>
> Though it looks like they want to be:
>
> http://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037
>
> "Note:
> Oracle Database currently treats a character value with a length of zero
> as null. However, this may not continue to be true in future releases, and
> Oracle recommends that you do not treat empty strings the same as nulls."
>
>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Thanks guys,
Just checking. (pun intended)

I'm guessing that Oracle when the way they did just for convenience. It's
hard to imagine a use case where you would NOT want to treat NULL the same
as you would a zero-length string. I would think that when a user writes
the query:
SELECT customer_number from TCUSTOMER where customer name is NULL; what
they actually want is:
SELECT customer_number from TCUSTOMER where customer name is NULL or
customer_name = '';

Oh well, it is what it is.
thanks again,
Mike