Thread: Replace NULL values

doesn't work either, cause the titles of my columns are very varied...

Thanks for any advice,

Stefan Schwarzer

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.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

> 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

On Thu, Sep 07, 2006 at 11:54:43AM +0200, Stefan Schwarzer wrote:
> 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

Ah, your data is denormalised. SQL doesn't handle that kind of data
very well at all. A user-defined function should be able to handle that
though (not pl/pgsql).

Hope this helps,

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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
>

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

On Thu, Sep 07, 2006 at 12:58:17PM +0200, Stefan Schwarzer wrote:
> 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

That's because nothing is equal to NULL. You say "values IS NULL".

And COALESCE(a,b) is pretty mutch: IF a IS NULL THEN b ELSE A

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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
>

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

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
>

On Thu, Sep 07, 2006 at 01:39:06PM +0200, Stefan Schwarzer wrote:
>
> You're right with the "is" versus "=" for NULL values. Unfortunately
> the coding for the mapserver does not allow an "IS" statement.

There's a hack for this; you need to turn it on in the config file.
I think it's called allow_null_equals.  It's ugly, but it might solve
this problem for you.

A


--
Andrew Sullivan  | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
        --Scott Morris