Re: Replace NULL values - Mailing list pgsql-general

Thanks for your suggestions.

You're right with the "is" versus "=" for NULL values. Unfortunately
the coding for the mapserver does not allow an "IS" statement.

Concerning the coalesce(datafield,-9999) it seems rather unusable for
me, if I have to explicitly stated each column, as a) for many tables
I have different column titles and b) there are up to 60 columns for
each table. There is no way to say something like
     coalesce(table.*, -9999)
I guess, no?

Thanks a lot!

Stefan

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend