agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David W Noon <david.w.noon@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?
Date: Fri, 3 Jun 2016 19:56:05 +0100
Message-ID: <5751D2C5.3080303@googlemail.com> (raw)
In-Reply-To: <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n7A@mail.gmail.com>
References: <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n7A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
-----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
view thread (5+ messages) latest in thread
Message-ID: <5751D2C5.3080303@googlemail.com>
Permalink: ../5751D2C5.3080303@googlemail.com/
Also on: postgresql.org/message-id/5751D2C5.3080303@googlemail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david.w.noon@googlemail.com
Subject: Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?
In-Reply-To: <5751D2C5.3080303@googlemail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox