pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [INTERFACES] selectecting not null varchars.
7+ messages / 5 participants
[nested] [flat]
* selectecting not null varchars.
@ 2000-02-24 19:46 Joseph Shraibman <jks@p1.selectacast.net>
2000-02-24 20:04 ` Re: [INTERFACES] selectecting not null varchars. Ed Loehr <eloehr@austin.rr.com>
2000-02-24 22:23 ` Re: [INTERFACES] selectecting not null varchars. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Joseph Shraibman @ 2000-02-24 19:46 UTC (permalink / raw)
To: pgsql-interfaces
I have a table with some text strings and I want to select row where the
text fields have a value in them.
I tried :
select * from directory where length(h) > 0 ;
and got:
ERROR: Null input to textlen
So I tried:
select * from directory where h != null AND length(h) > 0 ;
and got:
ERROR: parser: parse error at or near "and"
In fact this:
select * from directory where h != null ;
... gets you this:
ERROR: parser: parse error at or near ";"
Trying to select where it is equal to null works. Trying to select
where an int is not equal to a certain value works. But I cannot select
where a varchar (or int) is not equal to null.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] selectecting not null varchars.
2000-02-24 19:46 selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
@ 2000-02-24 20:04 ` Ed Loehr <eloehr@austin.rr.com>
2000-02-24 15:50 ` Re: [INTERFACES] selectecting not null varchars. Mike Mascari <mascarm@mascari.com>
2000-02-24 20:40 ` Re: [INTERFACES] selectecting not null varchars. Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1 sibling, 2 replies; 7+ messages in thread
From: Ed Loehr @ 2000-02-24 20:04 UTC (permalink / raw)
To: Joseph Shraibman <jks@p1.selectacast.net>; +Cc: pgsql-interfaces
Joseph Shraibman wrote:
>
> select * from directory where h != null ;
> ... gets you this:
> ERROR: parser: parse error at or near ";"
Postgresql's SQL "not equal" operator is "<>"...Try this:
select * from directory where h <> null;
Cheers,
Ed Loehr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] selectecting not null varchars.
2000-02-24 19:46 selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
2000-02-24 20:04 ` Re: [INTERFACES] selectecting not null varchars. Ed Loehr <eloehr@austin.rr.com>
@ 2000-02-24 15:50 ` Mike Mascari <mascarm@mascari.com>
2000-02-24 23:50 ` Re: [INTERFACES] selectecting not null varchars. Ed Loehr <eloehr@austin.rr.com>
1 sibling, 1 reply; 7+ messages in thread
From: Mike Mascari @ 2000-02-24 15:50 UTC (permalink / raw)
To: Ed Loehr <eloehr@austin.rr.com>; +Cc: Joseph Shraibman <jks@p1.selectacast.net>; pgsql-interfaces
Ed Loehr wrote:
>
> Joseph Shraibman wrote:
> >
> > select * from directory where h != null ;
> > ... gets you this:
> > ERROR: parser: parse error at or near ";"
>
> Postgresql's SQL "not equal" operator is "<>"...Try this:
>
> select * from directory where h <> null;
>
> Cheers,
> Ed Loehr
>
Does that work? I remember when the equality with NULL was added
because of non-compliant queries being generated from Access. I
didn't know inequality was implemented as well. Formal SQL is:
SELECT * FROM directory WHERE h IS NOT NULL;
Mike Mascari
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] selectecting not null varchars.
2000-02-24 19:46 selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
2000-02-24 20:04 ` Re: [INTERFACES] selectecting not null varchars. Ed Loehr <eloehr@austin.rr.com>
2000-02-24 15:50 ` Re: [INTERFACES] selectecting not null varchars. Mike Mascari <mascarm@mascari.com>
@ 2000-02-24 23:50 ` Ed Loehr <eloehr@austin.rr.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ed Loehr @ 2000-02-24 23:50 UTC (permalink / raw)
To: Mike Mascari <mascarm@mascari.com>; +Cc: Joseph Shraibman <jks@p1.selectacast.net>; pgsql-interfaces
Mike Mascari wrote:
>
> Someone wrote:
> >
> > Joseph Shraibman wrote:
> > >
> > > select * from directory where h != null ;
> > > ... gets you this:
> > > ERROR: parser: parse error at or near ";"
> >
> > Postgresql's SQL "not equal" operator is "<>"...Try this:
> >
> > select * from directory where h <> null;
>
> Does that work?
No, it does not work. Whoever said that must've not had their
afternoon nap.
> I remember when the equality with NULL was added
> because of non-compliant queries being generated from Access. I
> didn't know inequality was implemented as well. Formal SQL is:
>
> SELECT * FROM directory WHERE h IS NOT NULL;
You are right. That guy was asleep. [Mea culpa...]
Cheers,
Ed Loehr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] selectecting not null varchars.
2000-02-24 19:46 selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
2000-02-24 20:04 ` Re: [INTERFACES] selectecting not null varchars. Ed Loehr <eloehr@austin.rr.com>
@ 2000-02-24 20:40 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2000-02-24 21:46 ` Re: [INTERFACES] selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
1 sibling, 1 reply; 7+ messages in thread
From: Ross J. Reedstrom @ 2000-02-24 20:40 UTC (permalink / raw)
To: Ed Loehr <eloehr@austin.rr.com>; +Cc: Joseph Shraibman <jks@p1.selectacast.net>; pgsql-interfaces
On Thu, Feb 24, 2000 at 02:04:40PM -0600, Ed Loehr wrote:
> Joseph Shraibman wrote:
> >
> > select * from directory where h != null ;
> > ... gets you this:
> > ERROR: parser: parse error at or near ";"
>
> Postgresql's SQL "not equal" operator is "<>"...Try this:
>
> select * from directory where h <> null;
Which is still throw an error. and the SQL standard way to test for
NOT NULL is:
SELECT * FROM directory WHERE h IS NOT NULL;
Here's an example:
test=> \d test
Table = test
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| date | int4 default now ( ) | 4 |
| t | text | var |
+----------------------------------+----------------------------------+-------+
test=> insert into test (t) values ('');
INSERT 869922 1
test=> select * from test;
date|t
---------+-----
949357548|two
949357551|later
951424534|
-1|one
951424715|
(5 rows)
test=> select * from test where t is not null;
date|t
---------+-----
949357548|two
949357551|later
-1|one
951424715|
(4 rows)
test=>
test=> select * from test where length(t) >0;
ERROR: Null input to textlen
test=> select * from test where t is not null and length(t) >0;
date|t
---------+-----
949357548|two
949357551|later
-1|one
(3 rows)
test=>
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] selectecting not null varchars.
2000-02-24 19:46 selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
2000-02-24 20:04 ` Re: [INTERFACES] selectecting not null varchars. Ed Loehr <eloehr@austin.rr.com>
2000-02-24 20:40 ` Re: [INTERFACES] selectecting not null varchars. Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
@ 2000-02-24 21:46 ` Joseph Shraibman <jks@p1.selectacast.net>
0 siblings, 0 replies; 7+ messages in thread
From: Joseph Shraibman @ 2000-02-24 21:46 UTC (permalink / raw)
To: pgsql-interfaces
"Ross J. Reedstrom" wrote:
> On Thu, Feb 24, 2000 at 02:04:40PM -0600, Ed Loehr wrote:
> > Joseph Shraibman wrote:
> > >
> > > select * from directory where h != null ;
> > > ... gets you this:
> > > ERROR: parser: parse error at or near ";"
> >
> > Postgresql's SQL "not equal" operator is "<>"...Try this:
> >
> > select * from directory where h <> null;
>
That didn't work.
>
> Which is still throw an error. and the SQL standard way to test for
> NOT NULL is:
>
> SELECT * FROM directory WHERE h IS NOT NULL;
>
That did. Thanks all.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] selectecting not null varchars.
2000-02-24 19:46 selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
@ 2000-02-24 22:23 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2000-02-24 22:23 UTC (permalink / raw)
To: Joseph Shraibman <jks@p1.selectacast.net>; +Cc: pgsql-interfaces
Joseph Shraibman <jks@p1.selectacast.net> writes:
> select * from directory where h != null AND length(h) > 0 ;
The correct, SQL92-approved spelling for this condition is
WHERE h IS NOT NULL AND ...
We also accept the abbreviation "h NOTNULL", although that's not a
standard AFAIK. And of course you could do "NOT(h IS NULL)".
Certain Microsoft products that have difficulty grasping the concept of
NULL allow you to write these conditions with "=" or "!=", but in fact
any ordinary comparison operator should yield NULL out if either input
is NULL, so Microsoft is violating the letter and spirit of the spec by
accepting that.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-02-24 23:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-24 19:46 selectecting not null varchars. Joseph Shraibman <jks@p1.selectacast.net>
2000-02-24 20:04 ` Ed Loehr <eloehr@austin.rr.com>
2000-02-24 15:50 ` Mike Mascari <mascarm@mascari.com>
2000-02-24 23:50 ` Ed Loehr <eloehr@austin.rr.com>
2000-02-24 20:40 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2000-02-24 21:46 ` Joseph Shraibman <jks@p1.selectacast.net>
2000-02-24 22:23 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox