Thread: "IS NOT NULL" != "NOT NULL"
Just a question that's stems from curiosity, but:
db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
count
-------
0
(1 row)
db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
count
-------
1242
(1 row)
Why aren't those the same? Seems like the IS would be an extra word
that's not necessarily needed. ::shrug:: -sc
--
Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
> count
> -------
> 0
> (1 row)
> db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
> count
> -------
> 1242
> (1 row)
> Why aren't those the same? Seems like the IS would be an extra word
> that's not necessarily needed. ::shrug:: -sc
This is more than a tad hard to believe, considering that the parser
converts both of these constructs into the same internal representation:
| a_expr NOTNULL
{ $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
| a_expr IS NOT NULL_P
{ $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
Can you provide a reproducible example where the results are different?
regards, tom lane
> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL; > | a_expr NOTNULL Are NOT NULL and NOTNULL really the same? Greg
"Gregory Wood" <gregw@com-stock.com> writes:
> db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
>> | a_expr NOTNULL
> Are NOT NULL and NOTNULL really the same?
I assumed he meant "timestamp_col NOTNULL", because as given it would
certainly go no further than a parse error.
regards, tom lane
Hi Rudy As far as I know there is no book about PHP and PostgreSQL. You find some stuff at http://www.phpbuilder.com (if it is still online?) and at http://www.newbienetwork.com there are some tutorials. A downloadable tutoial is at http://users1.50megs.com/tomcat/postgresql-tutorial/ . Greetings Conni
Cornelia Boenigk wrote: > Hi Rudy > > As far as I know there is no book about PHP and PostgreSQL. > You find some stuff at http://www.phpbuilder.com (if it is still > online?) > and at http://www.newbienetwork.com there are some tutorials. > A downloadable tutoial is at > http://users1.50megs.com/tomcat/postgresql-tutorial/ . I have a book here: Building Database Applications on the Web Using PHP3 by Craig Hilton It does use PostgreSQL. I was not really impressed by it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce's book on PG is great and I'd read through the PHP manual's PostgreSQL section : http://www.php.net/manual/en/ref.pgsql.php - included are examples of how to use the functions listed.. -Mitch ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "Cornelia Boenigk" <c@cornelia-boenigk.de> Cc: "PostgreSQL-General" <pgsql-general@postgresql.org> Sent: Friday, January 18, 2002 2:42 PM Subject: Re: [GENERAL] Need book on PHP and PostgreSQL > Cornelia Boenigk wrote: > > Hi Rudy > > > > As far as I know there is no book about PHP and PostgreSQL. > > You find some stuff at http://www.phpbuilder.com (if it is still > > online?) > > and at http://www.newbienetwork.com there are some tutorials. > > A downloadable tutoial is at > > http://users1.50megs.com/tomcat/postgresql-tutorial/ . > > I have a book here: > > Building Database Applications on the Web Using PHP3 by Craig > Hilton > > It does use PostgreSQL. I was not really impressed by it. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Chris has also written a bunch of tutorials about using PostgreSQL and PHP together : http://pgsql.designmagick.com/tutorial.php They are the ones which were on NewbieNetwork.net, before it started having design troubles. I'll be updating the techdocs site later on today with these new links. :) Regards and best wishs, Justin Clift Mitch Vincent wrote: > > Bruce's book on PG is great and I'd read through the PHP manual's PostgreSQL > section : http://www.php.net/manual/en/ref.pgsql.php - included are examples > of how to use the functions listed.. > > -Mitch > > ----- Original Message ----- > From: "Bruce Momjian" <pgman@candle.pha.pa.us> > To: "Cornelia Boenigk" <c@cornelia-boenigk.de> > Cc: "PostgreSQL-General" <pgsql-general@postgresql.org> > Sent: Friday, January 18, 2002 2:42 PM > Subject: Re: [GENERAL] Need book on PHP and PostgreSQL > > > Cornelia Boenigk wrote: > > > Hi Rudy > > > > > > As far as I know there is no book about PHP and PostgreSQL. > > > You find some stuff at http://www.phpbuilder.com (if it is still > > > online?) > > > and at http://www.newbienetwork.com there are some tutorials. > > > A downloadable tutoial is at > > > http://users1.50megs.com/tomcat/postgresql-tutorial/ . > > > > I have a book here: > > > > Building Database Applications on the Web Using PHP3 by Craig > > Hilton > > > > It does use PostgreSQL. I was not really impressed by it. > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
>>>Tom Lane said:
> Sean Chittenden <sean@chittenden.org> writes:
> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
> > count
> > -------
> > 0
> > (1 row)
>
>
> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
> > count
> > -------
> > 1242
> > (1 row)
>
> > Why aren't those the same? Seems like the IS would be an extra word
> > that's not necessarily needed. ::shrug:: -sc
>
> This is more than a tad hard to believe, considering that the parser
> converts both of these constructs into the same internal representation:
>
> | a_expr NOTNULL
> { $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
> | a_expr IS NOT NULL_P
> { $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
>
> Can you provide a reproducible example where the results are different?
On 7.1.3 the results are:
customer=# select count(*) from croute where archived_at not null;
ERROR: parser: parse error at or near "null"
customer=# select count(*) from croute where archived_at is not null;
count
-------
437
(1 row)
archived_at is timestamp
What Postgres version is this?
By the way,
customer=# select count(*) from croute where archived_at != NULL;
count
-------
0
(1 row)
which may or may not be wrong :-) NULL is thereoretically not possible to be
compared to anything, but...
Daniel
> Sean Chittenden <sean@chittenden.org> writes:
> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
> > count
> > -------
> > 0
> > (1 row)
>
>
> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
> > count
> > -------
> > 1242
> > (1 row)
>
> > Why aren't those the same? Seems like the IS would be an extra word
> > that's not necessarily needed. ::shrug:: -sc
>
> This is more than a tad hard to believe, considering that the parser
> converts both of these constructs into the same internal representation:
>
> | a_expr NOTNULL
> { $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
> | a_expr IS NOT NULL_P
> { $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
>
> Can you provide a reproducible example where the results are different?
Sure thing.
test_pgsql=# SELECT version();
version
--------------------------------------------------------------
PostgreSQL 7.1.3 on i386--freebsd4.4, compiled by GCC 2.95.3
(1 row)
test_pgsql=# CREATE TABLE test (
test_pgsql(# col1 SERIAL,
test_pgsql(# col2 INT,
test_pgsql(# PRIMARY KEY(col1));
CREATE
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (1);
test_pgsql=# INSERT INTO test (col2) values (2);
test_pgsql=# INSERT INTO test (col2) values (3);
test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 IS NOT NULL;
count
-------
3
(1 row)
test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
count
-------
3
(1 row)
test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
count
-------
0
(1 row)
Any ideas? -sc
--
Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
> count
> -------
> 3
> (1 row)
> test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
> count
> -------
> 0
> (1 row)
These are not the same thing. See any of the past discussions about
why "null = null" and "null != null" and so forth do not do what a
novice might expect.
regards, tom lane
> > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL; > > count > > ------- > > 3 > > (1 row) > > > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL; > > count > > ------- > > 0 > > (1 row) > > These are not the same thing. See any of the past discussions about > why "null = null" and "null != null" and so forth do not do what a > novice might expect. http://www.postgresql.org/idocs/index.php?functions-comparison.html The archives appear to be broken, but the above link seems to explain things well enough. I still can't grok the rationale as to why NULL is interpreted as unknown and not interpreted as empty. I understand that you can't compare two values that are unknown until the unknowns are known. However why null isn't interpreted as empty is something I haven't grasped. '' is different than empty ('' is a defined string that's 0 characters in length), which is different than null (unknown). In my mind: "col2 != NULL" is the same as "col2 IS NOT NULL", but I fully understand why "col2 = NULL" is an invalid statement. Not a biggie, just a source of curiosity. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> In my mind: "col2 != NULL" is the same as "col2 IS NOT
> NULL", but I fully understand why "col2 = NULL" is an invalid
> statement.
To me, "col2 != NULL" means "NOT (col2 = NULL)". Does that help it
make more sense to you?
The reason SQL has the special IS NULL and IS NOT NULL constructs
is exactly that you can't do anything useful with "foo = NULL" or
"foo != NULL".
If you want to get into language-lawyering: I believe that in pure
SQL92 you can't even validly write an unadorned NULL as a constant
in an expression; you're supposed to cast it to some type, viz
"CAST(NULL AS something)". Postgres is lax about this since we have
ambiguous-type resolution machinery in the parser anyway. Perhaps
the reason why certain other DBMSes thought they could get away with
interpreting "foo = NULL" as "foo IS NULL" is that "foo = NULL" is
illegal according to the strict text of the standard, and thus arguably
doesn't have a standardized meaning; even though anyone who's grasped
the SQL rules for NULL would expect it to yield NULL.
regards, tom lane
On Sat, 19 Jan 2002, Sean Chittenden wrote: > > > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL; > > > count > > > ------- > > > 3 > > > (1 row) > > > > > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL; > > > count > > > ------- > > > 0 > > > (1 row) > > > > These are not the same thing. See any of the past discussions about > > why "null = null" and "null != null" and so forth do not do what a > > novice might expect. > > http://www.postgresql.org/idocs/index.php?functions-comparison.html > > The archives appear to be broken, but the above link seems to explain > things well enough. I still can't grok the rationale as to why NULL > is interpreted as unknown and not interpreted as empty. I understand > that you can't compare two values that are unknown until the unknowns > are known. However why null isn't interpreted as empty is something I > haven't grasped. '' is different than empty ('' is a defined string > that's 0 characters in length), which is different than null > (unknown). In my mind: "col2 != NULL" is the same as "col2 IS NOT > NULL", but I fully understand why "col2 = NULL" is an invalid > statement. Not a biggie, just a source of curiosity. -sc I had a hell of a time with that at first too. What you need to understand is that NULL isn't necessarily empty as you would expect. It's not the same as a null string - a null string actually has a real definition, a zero length string. I probably didn't help much. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Vince Vielhaber <vev@michvhf.com> writes:
> I had a hell of a time with that at first too. What you need to
> understand is that NULL isn't necessarily empty as you would expect.
> It's not the same as a null string - a null string actually has a
> real definition, a zero length string. I probably didn't help much.
Right. The common phrase "null string" doesn't help to reduce the
confusion any; perhaps "empty string" for zero-length string would
be a better phrase to use when you are working with SQL. NULL is
absolutely not the same as an empty string. NULL is outside the
domain of normal data for every datatype; it is better thought of
as the absence of a value than as any particular value.
I've been told that Oracle fails to distinguish empty strings from
NULL, which if true is a clear violation of the SQL specification.
If you're used to Oracle then that might help explain your confusion :-(
Another problem is that SQL's boolean operations act as though NULL
is the logical value UNKNOWN, rather than explicitly setting up a
boolean datatype with the three allowed values TRUE, FALSE, UNKNOWN.
While the rules for propagation of NULL happen to be similar to the
results that logic dictates you get for UNKNOWN, this is still a kind
of type pun, and it doesn't help to reduce the confusion any.
regards, tom lane
> Another problem is that SQL's boolean operations act as though NULL
> is the logical value UNKNOWN, rather than explicitly setting up a
> boolean datatype with the three allowed values TRUE, FALSE, UNKNOWN.
> While the rules for propagation of NULL happen to be similar to the
> results that logic dictates you get for UNKNOWN, this is still a kind
> of type pun, and it doesn't help to reduce the confusion any.
Sorry for not mentioning section, it is Customizing Queries/Using NULL
values. Content on the web:
http://www.postgresql.org/docs/awbook.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> I've been told that Oracle fails to distinguish empty strings from
> NULL, which if true is a clear violation of the SQL specification.
> If you're used to Oracle then that might help explain your confusion :-(
>
> Another problem is that SQL's boolean operations act as though NULL
> is the logical value UNKNOWN, rather than explicitly setting up a
> boolean datatype with the three allowed values TRUE, FALSE, UNKNOWN.
> While the rules for propagation of NULL happen to be similar to the
> results that logic dictates you get for UNKNOWN, this is still a kind
> of type pun, and it doesn't help to reduce the confusion any.
My book does deal with this NULL distinction:
http://www.postgresql.org/docs/awbook.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026