Thread: selectecting not null varchars.

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.

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

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

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

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

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

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