Subject: Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?
To: pgsql-sql@postgresql.org
References: 
 <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n7A@mail.gmail.com>
From: David W Noon <david.w.noon@googlemail.com>
Organization: Luton Operatic Society
Message-ID: <5751D2C5.3080303@googlemail.com>
Date: Fri, 3 Jun 2016 19:56:05 +0100
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n7A@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


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