Re: Replace NULL values - Mailing list pgsql-general

I have an internet map server connected to my database. Until now,
"no data" fields within the table were filled with a "-9999", i.e.
"-9999" equalled "no data available".

Now, for displaying a map with different classes (red for values from
0-100, green for values from 100-200....) I need to build as well a
class for "no data" (which is displayed in grey). Until now that
worked perfectly well with the "-9999" values. But since I inserted a
couple of new countries (which do not find any corresponding values
in the tables, as they don't yet exist), I receive the usual "-9999"
plus "NULL" values. Both should be considered as "no data" and thus
displayed in grey.

Unfortunately the mapserver can't deal with NULL values. So, I can't
build a class saying
    if values = NULL do something
but instead it only works with "fake" NULL values as -9999
    if values = -9999 do something

Stef

> Aside from your database structure being problematic, what are you
> trying to accomplish?
> In other words, what do you want to replace the nulls with and in
> what circumstance?
> I imagine your table looks like this
> ID,country,1950,1951,1952,1953,....
> 1   usa    50   null  70   10
> 2  canada  10   45   null   4
>
> Please mention what you would like to do with this?
>
>
> Stefan Schwarzer wrote:
>>> On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote:
>>>> Hi there,
>>>>
>>>> is there a simple way to replace NULL values in multiple columns
>>>> within the SQL statement? I changed the underlaying country
>>>> template
>>>> of your database; so now there are a couple of NULL values when I
>>>> join the stats-table with the country table. Unfortunately, my
>>>> queries have always multiple (year) columns, so I can't do a
>>>> kind of
>>>> manual replace.
>>>>
>>>> I found that the COALESCE command does something like this, but I
>>>> couldn't figure out how this works.
>>>
>>> Yes, COALESCE replaces NULLs, however your examples have neither
>>> NULLs
>>> nor use COALESCE, so I don't understand what your question is.
>>>
>>> Please repost with an actual example of your problem.
>> As I said, I couldn't figure out how COALESCE would work on
>> multiple columns (without naming them explicitly).
>> So, say I have a table with columns for each year between 1970 and
>> 2005. For specific countries the values might be NULL, depending
>> if the statistical table has been updated recently (then they will
>> have a value), or not (then they will be NULL). A sample query
>> would thus be something like:
>>     SELECT * FROM pop_density
>>  ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>       choose an index scan if your joining column's datatypes do not
>>       match
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org