Re: Replace NULL values - Mailing list pgsql-general

It seems like the mapserver is quite inflexible. Maybe there are options
to loosen it up a little? (I've found that pouring beer into the
keyboard after a long week does not help.)

I would venture that your best option is to write a quick pgpsql
function that goes over all the fields in the table, writes an alter
statement that gives them a default value and an update statement that
changes the null to -9999. Then any new fields that are created must be
created with the default.

All the field names are in the pg_attribute table and the table name is
in the pg_class table. Spend a couple minutes figuring out how to tell
the fields from the indexes and your code should be a walk in the park.

Sim

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