-----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-----