Обсуждение: NOT NULL CHECK (mycol !='') :good idea? bad idea?
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
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 evenmycol VARCHAR(20) CHECK (length(mycol) > 0)
That seems like the best choice. Equality checks should be faster than length detection.
Dave
-----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-----
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 > (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. 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.klaver@aklaver.com
On Fri, Jun 3, 2016 at 12:05 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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
(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.
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.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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; 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