Postgres Pro
Downloads
Home   >   mailing lists

Thread: IS NULL

IS NULL

From
David Link
Date:
How to check a column value for IS NULL?

I would like to format a string as a date iff it is not null:

This frag returns a bogus date when column value is null:

  to_char(to_date (theatre_reldate, 'DDMMYYYY'), 'DD Mon YYYY')

So I'm looking for something of this sort:

 (case when not is null (t.theatre_reldate) then
    to_char(to_date (theatre_reldate, 'DDMMYYYY'), 'DD Mon YYYY')
    end) as theatre_reldate

thanks, david

Re: IS NULL

From
Tom Lane
Date:
David Link <dlink@soundscan.com> writes:
> This frag returns a bogus date when column value is null:
>   to_char(to_date (theatre_reldate, 'DDMMYYYY'), 'DD Mon YYYY')

Uh, what's bogus about it?  I get a NULL result.

> So I'm looking for something of this sort:
>  (case when not is null (t.theatre_reldate) then

It's spelled "something IS NULL" or "something IS NOT NULL".

            regards, tom lane

Re: IS NULL

From
David Link
Date:
Tom Lane wrote:
>
> David Link <dlink@soundscan.com> writes:
> > This frag returns a bogus date when column value is null:
> >   to_char(to_date (theatre_reldate, 'DDMMYYYY'), 'DD Mon YYYY')
>
> Uh, what's bogus about it?  I get a NULL result.

The data in my column was bogus and not of the form 'DDMMYYYY'.  Sorry
Tom, my mistake.   Actually, it was of the form YYYY and it was causing
the query to blow up with:

psql:show_title.sql:13: pqReadData() -- backend closed the channel
unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
psql:show_title.sql:13: connection to server was lost

>
> > So I'm looking for something of this sort:
> >  (case when not is null (t.theatre_reldate) then
>
> It's spelled "something IS NULL" or "something IS NOT NULL".
>

Thanks, I had trouble finding this in the on-line manuals.  I couldn't
find it in Chapter 4 of the User Guide, Functions and Operators.

-David

Re: IS NULL

From
Tom Lane
Date:
David Link <dlink@soundscan.com> writes:
> The data in my column was bogus and not of the form 'DDMMYYYY'.  Sorry
> Tom, my mistake.   Actually, it was of the form YYYY and it was causing
> the query to blow up with:

> psql:show_title.sql:13: pqReadData() -- backend closed the channel
> unexpectedly.

This is a bug.  What version are you using?  Could you supply
an exact example of a value that makes it crash?

            regards, tom lane

Re: IS NULL

From
Tom Lane
Date:
David Link <dlink@soundscan.com> writes:
> Tom Lane wrote:
>> This is a bug.  What version are you using?  Could you supply
>> an exact example of a value that makes it crash?

> bobcat=# select upckey, upc, title, theatre_reldate, genre, subgenre
> from title
>  where upckey = 77703;
>  upckey |     upc      |   title    | theatre_reldate | genre | subgenre
> --------+--------------+------------+-----------------+-------+----------
>   77703 | 339660005072 | HOLLOW MAN | 2000            | 944   | x
> (1 row)

Could I trouble you for the exact datatypes of these columns, too?

            regards, tom lane

Re: IS NULL

From
David Link
Date:
Tom Lane wrote:
>
> David Link <dlink@soundscan.com> writes:
> > The data in my column was bogus and not of the form 'DDMMYYYY'.  Sorry
> > Tom, my mistake.   Actually, it was of the form YYYY and it was causing
> > the query to blow up with:
>
> > psql:show_title.sql:13: pqReadData() -- backend closed the channel
> > unexpectedly.
>
> This is a bug.  What version are you using?  Could you supply
> an exact example of a value that makes it crash?

pg 7.0.3 (running on mandrake 8.0 / 2.4.3 kernal)

Query 1 - All's Well:

bobcat=# select upckey, upc, title, theatre_reldate, genre, subgenre
from title
 where upckey = 77703;
 upckey |     upc      |   title    | theatre_reldate | genre | subgenre
--------+--------------+------------+-----------------+-------+----------
  77703 | 339660005072 | HOLLOW MAN | 2000            | 944   | x
(1 row)

Queary 2 - Bogus date (garbage in, garbage out):

bobcat=# select upckey, upc, title, to_char(to_date (theatre_reldate,
'DDMMYYYY
'), 'DD Mon YYYY') from title where upckey = 77703;
 upckey |     upc      |             title              |   to_char
--------+--------------+--------------------------------+-------------
  77703 | 339660005072 | HOLLOW MAN                     | 20 Dec 0002
(1 row)

Query 3 - Add a column to query and get more bad data:

bobcat=# select upckey, upc, title, to_char(to_date (theatre_reldate,
'DDMMYYYY
'), 'DD Mon YYYY'), genre from title where upckey = 77703;
 upckey |     upc      |             title              |   to_char   |
genre
--------+--------------+--------------------------------+-------------+-------
  77703 | 339660005072 | HOLLOW MAN                     | 20 Dec 0002 |
¨a
(1 row)


Query 4 - Add another column and it blows up:

bobcat=# select upckey, upc, title, to_char(to_date (theatre_reldate,
'DDMMYYYY'), 'DD Mon YYYY'), genre, subgenre from title where upckey =
77703;
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#

After repeating the error once or twice, It then gives this error for
this query and others involving this row:

FATAL 1:  Memory exhausted in AllocSetAlloc()
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Scared, I shutdown the database and started it up again and it seems to
be OK again.

Thanks, David

Re: IS NULL

From
David Link
Date:
Tom Lane wrote:
>
> David Link <dlink@soundscan.com> writes:
> > Tom Lane wrote:
> >> This is a bug.  What version are you using?  Could you supply
> >> an exact example of a value that makes it crash?
>
> > bobcat=# select upckey, upc, title, theatre_reldate, genre, subgenre
> > from title
> >  where upckey = 77703;
> >  upckey |     upc      |   title    | theatre_reldate | genre | subgenre
> > --------+--------------+------------+-----------------+-------+----------
> >   77703 | 339660005072 | HOLLOW MAN | 2000            | 944   | x
> > (1 row)
>
> Could I trouble you for the exact datatypes of these columns, too?
>
>                         regards, tom lane

Sure thing:  varchar(4)

Please note, I've  inherrited this.  The original system was all flat
files and I'm in the process of migrating to the relational world.

bobcat=# \d title
                                 Table "title"
    Attribute    |     Type     |
Modifier
-----------------+--------------+----------------------------------------------
 upckey          | integer      | not null default
nextval('upckey_seq'::text)
 upc             | varchar(12)  | not null
 media           | varchar(2)   | not null
 title           | varchar(30)  | not null
 artist          | varchar(30)  |
 distributor     | varchar(10)  |
 retail_reldate  | varchar(10)  |
 theatre_reldate | varchar(4)   |
 genre           | varchar(10)  |
 subgenre        | varchar(10)  |
 rating          | varchar(10)  |
 animation       | varchar(2)   |
 brand           | varchar(10)  |
 orig_lang       | varchar(10)  |
 rel_lang        | varchar(10)  |
 language_usage  | varchar(2)   |
 run_time        | numeric(4,0) |
 screen_format   | varchar(2)   |
 msrp            | varchar(7)   |
 source          | varchar(2)   |
 source_dvd      | varchar(2)   |
 rel_status      | varchar(2)   |
 box_office      | varchar(10)  |
 box_office_cd   | varchar(10)  |
 edi_code        | varchar(10)  |
 c_mode          | varchar(2)   |
 uncv            | varchar(2)   |
 blob            | varchar(2)   |
 datein          | varchar(8)   |
 dateup          | varchar(8)   |
Index: title_pkey

Re: IS NULL

From
Tom Lane
Date:
David Link <dlink@soundscan.com> writes:
>> This is a bug.  What version are you using?  Could you supply
>> an exact example of a value that makes it crash?

> pg 7.0.3 (running on mandrake 8.0 / 2.4.3 kernal)

Okay, I've looked at the CVS logs and I believe you are stumbling over
some nasty little bugs in the 7.0.* versions of to_timestamp (which is
invoked by to_date) and to_char.  I would strongly recommend an upgrade
to 7.1.*.  In particular, 7.0's to_timestamp has the ugly habit of
scribbling on its input data, which is bad news in any case but
particularly so when applied directly to a table column :-(.  If you
use it in 7.0 you are very likely to suffer unrecoverable data loss.

            regards, tom lane