[SQL] NOT NULL CHECK (mycol !='') :good idea? bad idea?

[Michael Moore]

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. 

To make Postgres perform an equivalent column edit, I am considering defining table columns like  ... mycol VARCHAR(20) NOT NULL CHECK (mycol !='')

Is there any drawback to this? Is there a better way to do it? Any thoughts? how about ....

mycol VARCHAR(20) NOT NULL CHECK (length(mycol) > 0)

or even

mycol VARCHAR(20)                   CHECK (length(mycol) > 0)

tia,

Mike

[David G. Johnston]

​That seems like the best choice.  Equality checks should be faster than length detection.

Dave

​

 

[David W Noon]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 3 Jun 2016 11:16:33 -0700, Michael Moore
(michae...@gmail.com) wrote about "[SQL] NOT NULL CHECK (mycol
!='') :good idea? bad idea?" (in
<CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n...@mail.gmail.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.

> To make Postgres perform an equivalent column edit, I am
> considering defining table columns like ... mycol VARCHAR(20) NOT
> NULL CHECK (mycol !='')
>
> Is there any drawback to this? Is there a better way to do it? Any
> thoughts? how about .... mycol VARCHAR(20) NOT NULL CHECK
> (length(mycol) > 0)

This looks like the best, as it checks the NULL status first (cheap
check) and then the length, which is also determined quite quickly
from the varlena descriptor.

> or even mycol VARCHAR(20) CHECK (length(mycol) >
> 0)

I'm not sure what result LENGTH() returns if a NULL is supplied, but I
would guess that it's NULL. This would make the comparison NULL > 0,
which could be anything but probably FALSE. I would assert NOT NULL in
the declaration to ensure that NULL values are eliminated before
length checks.

I assume that the problem domain does not require the ability to enter
a zero-length string into that column, as this approach will replicate
Oracle's NOT NULL semantics for that column.
- --
Regards,

Dave [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
david....@googlemail.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iEYEARECAAYFAldR0sQACgkQogYgcI4W/5Qq4ACfRceTL7PRcG6F24A2nPzuxhui
0rYAn1PFHV0F2ivujaWk4mO6f3Gn7SMI
=eGoG
-----END PGP SIGNATURE-----


--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Adrian Klaver]

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
> (michae...@gmail.com) wrote about "[SQL] NOT NULL CHECK (mycol
> !='') :good idea? bad idea?" (in
> <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n...@mail.gmail.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...@aklaver.com

[Michael Moore]

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