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

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

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

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

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