Re: [GENERAL] Replace null values

[Ian Haywood]

On Tue, Mar 23, 2010 at 5:33 PM, Nilesh Govindarajan  wrote:
> On 03/23/2010 11:47 AM, John R Pierce wrote:
>>
>>> How to convert zero length string to null ?
>>
>>
>>
>> UPDATE yourtable SET name=NULL WHERE name='';
>>
>>
>
> No I don't want to replace it in the table. Just in the query result.
use a CASE construct.
select case name='' then null else name end from table where ..

Ian

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replace null values

[Nilesh Govindarajan]

On 03/23/2010 12:17 PM, Sreelatha G wrote:

Hi,
  select case when name='' then null end from table;
Thanks
Sreelatha
On Tue, Mar 23, 2010 at 12:03 PM, Nilesh Govindarajan mailto:li...@itech7.com>> wrote:

On 03/23/2010 11:47 AM, John R Pierce wrote:


How to convert zero length string to null ?




UPDATE yourtable SET name=NULL WHERE name='';



No I don't want to replace it in the table. Just in the query result.

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com 


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




Thanks !

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replace null values

[Nilesh Govindarajan]

On 03/23/2010 11:47 AM, John R Pierce wrote:



How to convert zero length string to null ?




UPDATE yourtable SET name=NULL WHERE name='';




No I don't want to replace it in the table. Just in the query result.

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replace null values

[John R Pierce]

How to convert zero length string to null ?




UPDATE yourtable SET name=NULL WHERE name='';



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replace null values

[Nilesh Govindarajan]

On 03/23/2010 09:47 AM, Osvaldo Kussama wrote:

2010/3/23 Nilesh Govindarajan:

Hi,

In my query, some rows have null values (length 0).


NULL or a zero lenght string?




I wish to replace them with some constant.

I think I am wrong somewhere in this query using coalesce():

select coalesce(u.name, 'anon'), nodecount from users u, ( select n.uid
userid, count(n.nid) nodecount from node n group by n.uid order by n.uid )
t1 where u.uid = t1.userid order by nodecount;

The output is same as that of without coalesce.




bdteste=# SELECT coalesce(NULL, 'anon'), coalesce('', 'anon');
  coalesce | coalesce
--+--
  anon |
(1 registro)

Osvaldo


It is a zero length string. Somebody on the list suggested to use CASE. 
It worked. Thanks anyways.


Got to learn about coalesce that it replaces null values and not zero 
length strings.


How to convert zero length string to null ?

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replace null values

[Nilesh Govindarajan]

On 03/23/2010 10:07 AM, John R Pierce wrote:

Nilesh Govindarajan wrote:

Hi,

In my query, some rows have null values (length 0).


a NULL value is not length 0, NULL is not the empty string, rather, NULL
is no value at all.

if you want to change a 0 length string to something, use a CASE or
something.

select CASE WHEN u.name = '' THEN 'anon' ELSE u.name, 







Thanks a lot ! It worked :)

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replace null values

[John R Pierce]

Nilesh Govindarajan wrote:

Hi,

In my query, some rows have null values (length 0).


a NULL value is not length 0, NULL is not the empty string, rather, NULL 
is no value at all.


if you want to change a 0 length string to something, use a CASE or 
something.


select CASE WHEN u.name = '' THEN 'anon' ELSE u.name,  





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Replace null values

[Nilesh Govindarajan]

Hi,

In my query, some rows have null values (length 0).

I wish to replace them with some constant.

I think I am wrong somewhere in this query using coalesce():

select coalesce(u.name, 'anon'), nodecount from users u, ( select n.uid 
userid, count(n.nid) nodecount from node n group by n.uid order by n.uid 
) t1 where u.uid = t1.userid order by nodecount;


The output is same as that of without coalesce.

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replace NULL values

[Andrew Sullivan]

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  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Replace NULL values

[Sim Zacks]

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 -. 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,-) 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.*, -)
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 - 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,-) as datafield,... and that 
will change all nulls to - 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 "-", i.e. 
"-" 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 "-" 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 "-" 
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 -
if values = - 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   usa50   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



---(end of broadcast)-

Re: [GENERAL] Replace NULL values

[Stefan Schwarzer]

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,-) 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.*, -)
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 - 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,-) as datafield,... and that  
will change all nulls to - 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 "-", i.e.  
"-" 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 "-" 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 "-" 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 -
if values = - 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   usa50   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

Re: [GENERAL] Replace NULL values

[Sim Zacks]

Once again Martijn is correct, and you have to use "is null" not "=null"
One thing you might want to consider is adding - 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,-) as datafield,... and that will 
change all nulls to - 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 "-", i.e. "-" 
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 "-" 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 "-" 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 -
if values = - 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   usa50   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

Re: [GENERAL] Replace NULL values

[Martijn van Oosterhout]

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 -
>   if values = - 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  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Replace NULL values

[Stefan Schwarzer]

I have an internet map server connected to my database. Until now,  
"no data" fields within the table were filled with a "-", i.e.  
"-" 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 "-" 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 "-"  
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 -
if values = - 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   usa50   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

Re: [GENERAL] Replace NULL values

[Sim Zacks]

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   usa50   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

Re: [GENERAL] Replace NULL values

[Martijn van Oosterhout]

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  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Replace NULL values

[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.


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

Re: [GENERAL] Replace NULL values

[Martijn van Oosterhout]

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  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] Replace NULL values

[Stefan Schwarzer]

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.And this solution which I found on the Net:=> SELECT * FROM test; a--- 1 2 3=> SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; a | case---+--- 1 | one 2 | two 3 | otherdoesn't work either, cause the titles of my columns are very varied...Thanks for any advice,Stefan Schwarzer        ___Stefan SchwarzerGIS & Data ManagementUNEP/DEWA/GRID-EuropeChemin des Anemones 11CH - 1219 ChatelaineSwitzerlandTel: (+41) 22.917.83.49Fax: (+41) 22.917.80.29Internet: http://geodata.grid.unep.ch/___