Thread

Re: isnull or coalesce isn't working

Liz Bartlett <khyri@idyllmtn.com> — 2000-06-05T03:36:36Z

>HI
>just started using postgres I want to do a select like the following.
>I have a table with two columns a and b, where sometimes data are missing. 
>I want to do:
>SELECT ifnull(a,b) FROM table;
>
>==>ERROR:  No such function 'ifnull' with the specified attributes
>
>tried the same thing with isnull and coalesce, same error.

I would try:

SELECT a, b FROM table WHERE (a ISNULL) OR (b ISNULL);

On a related note:

I have a table with a column of type LOGICAL. Some rows have 't', some
have 'f' and some have a NULL value.

I wanted to select all the rows that were not TRUE.

Using a WHERE clause of feespaid != 't' only returned the rows where
the value was 'f'. (As did other variations of a single condition,
such as NOT(feespaid = 't').)

In the end, I had to add in a second check for the NULL value:

SELECT entryid FROM entriesspring2000 WHERE ((feespaid != 't') OR (feespaid ISNULL))

This to me seems counterintuitive. Is there any way I can ask for
the rows that do not have a TRUE value without having two conditions?

--Liz

----------------------------------------------------------------------
Liz Bartlett*http://www.khyri.com/            110 E. Wilshire Ave.#G-1
Idyll Mountain Internet*http://www.idyllmtn.com/   Fullerton, CA 92832
Virtual Dog Show Co-ordinator*http://www.dogshow.com/   (714) 526-5656
Tibetan Mastiff Web Site*http://www.tibetanmastiffs.com/