agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedNOT NULL CHECK (mycol !='') :good idea? bad idea?
5+ messages / 4 participants
[nested] [flat]
* NOT NULL CHECK (mycol !='') :good idea? bad idea?
@ 2016-06-03 18:16 Michael Moore <michaeljmoore@gmail.com>
2016-06-03 18:37 ` Re: NOT NULL CHECK (mycol !='') :good idea? bad idea? David G. Johnston <david.g.johnston@gmail.com>
2016-06-03 18:56 ` Re: NOT NULL CHECK (mycol !='') :good idea? bad idea? David W Noon <david.w.noon@googlemail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Michael Moore @ 2016-06-03 18:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?
2016-06-03 18:16 NOT NULL CHECK (mycol !='') :good idea? bad idea? Michael Moore <michaeljmoore@gmail.com>
@ 2016-06-03 18:37 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2016-06-03 18:37 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Fri, Jun 3, 2016 at 2:16 PM, Michael Moore <michaeljmoore@gmail.com>
wrote:
> 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)
>
>
That seems like the best choice. Equality checks should be faster than
length detection.
Dave
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?
2016-06-03 18:16 NOT NULL CHECK (mycol !='') :good idea? bad idea? Michael Moore <michaeljmoore@gmail.com>
@ 2016-06-03 18:56 ` David W Noon <david.w.noon@googlemail.com>
2016-06-03 19:05 ` Re: NOT NULL CHECK (mycol !='') :good idea? bad idea? Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 5+ messages in thread
From: David W Noon @ 2016-06-03 18:56 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?
2016-06-03 18:16 NOT NULL CHECK (mycol !='') :good idea? bad idea? Michael Moore <michaeljmoore@gmail.com>
2016-06-03 18:56 ` Re: NOT NULL CHECK (mycol !='') :good idea? bad idea? David W Noon <david.w.noon@googlemail.com>
@ 2016-06-03 19:05 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-06-03 19:25 ` Re: NOT NULL CHECK (mycol !='') :good idea? bad idea? Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2016-06-03 19:05 UTC (permalink / raw)
To: David W Noon <david.w.noon@googlemail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NOT NULL CHECK (mycol !='') :good idea? bad idea?
2016-06-03 18:16 NOT NULL CHECK (mycol !='') :good idea? bad idea? Michael Moore <michaeljmoore@gmail.com>
2016-06-03 18:56 ` Re: NOT NULL CHECK (mycol !='') :good idea? bad idea? David W Noon <david.w.noon@googlemail.com>
2016-06-03 19:05 ` Re: NOT NULL CHECK (mycol !='') :good idea? bad idea? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2016-06-03 19:25 ` Michael Moore <michaeljmoore@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Moore @ 2016-06-03 19:25 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: David W Noon <david.w.noon@googlemail.com>; pgsql-sql
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 or
customer_name = '';
Oh well, it is what it is.
thanks again,
Mike
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-06-03 19:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-03 18:16 NOT NULL CHECK (mycol !='') :good idea? bad idea? Michael Moore <michaeljmoore@gmail.com>
2016-06-03 18:37 ` David G. Johnston <david.g.johnston@gmail.com>
2016-06-03 18:56 ` David W Noon <david.w.noon@googlemail.com>
2016-06-03 19:05 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-06-03 19:25 ` Michael Moore <michaeljmoore@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox