Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?

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