Обсуждение: 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
<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Fri, Jun 3, 2016 at 12:05 PM, Adrian Klaver<span dir="ltr"><<a href="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0px 0px0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><span class="">On06/03/2016 11:56 AM, David W Noon wrote:<br /><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">-----BEGIN PGP SIGNEDMESSAGE-----<br /> Hash: SHA1<br /><br /> On Fri, 3 Jun 2016 11:16:33 -0700, Michael Moore<br /> (<a href="mailto:michaeljmoore@gmail.com"target="_blank">michaeljmoore@gmail.com</a>) wrote about "[SQL] NOT NULL CHECK (mycol<br/> !='') :good idea? bad idea?" (in<br /> <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=<a href="mailto:n7A@mail.gmail.com"target="_blank">n7A@mail.gmail.com</a>>):<br /><br /><blockquote class="gmail_quote" style="margin:0px0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">In Oracle, a NOTNULL constraint on a table column of VARCHAR in<br /> essence says: "You need to put at least 1 character for a value".<br/> There is no such thing as a zero-length string in Oracle, it's<br /> either NULL or it has some characters.<br/></blockquote><br /> So Oracle is not compliant with ANSI standard SQL.<br /></blockquote><br /></span> Thoughit looks like they want to be:<br /><br /><a href="http://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037"rel="noreferrer" target="_blank">http://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037</a><br/><br /> "Note:<br /> OracleDatabase currently treats a character value with a length of zero as null. However, this may not continue to be truein future releases, and Oracle recommends that you do not treat empty strings the same as nulls."<span class=""><fontcolor="#888888"><br /><br /><br /><br /><br /><br /> -- <br /> Adrian Klaver<br /><a href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a></font></span><div class=""><div class="h5"><br/><br /><br /> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"rel="noreferrer" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></div></div></blockquote></div><br /></div><div class="gmail_extra">Thanksguys,</div><div class="gmail_extra">Just checking. (pun intended)</div><div class="gmail_extra"><br/></div><div class="gmail_extra">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:<br />SELECT customer_number from TCUSTOMER where customer name isNULL; what they actually want is:</div><div class="gmail_extra">SELECT customer_number from TCUSTOMER where customer nameis NULL or customer_name = ''; <br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra">Oh well, itis what it is. </div><div class="gmail_extra">thanks again,</div><div class="gmail_extra">Mike</div><div class="gmail_extra"><br/></div></div>