Re: Replace NULL values - Mailing list pgsql-general

Once again Martijn is correct, and you have to use "is null" not "=null"
One thing you might want to consider is adding -9999 as a default value
in the table so that when new data is entered it with a null it
automatically gets the correct value.
Using coalesce with the value will probably be the simplest for you.
Try select ...,coalesce(datafield,-9999) as datafield,... and that will
change all nulls to -9999 and give it the correct fieldname.

Stefan Schwarzer wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>