Postgresql – Insert column name as value

database-designinsertpostgresqlschema

Consider the following, in PostgreSQL:

drop table test ;
create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ;
insert into test values ('red',1,2,3) ;

I would like results2 to contain the value blue, indicating that the maximum value for the integer columns occurs in column blue. This can be achieved by running an UPDATE or a function at a later time.

How do I insert the column name as a value? As an example, the row above would be updated such that it contains:

'red', 1, 2, 3, 'blue'

There will not be NULL values for the integer columns and one of the integers will always be the largest.

(See also here.)

Best Answer

You could create a view that looks like something like this:

create view test_view
as
select a,b,c,d,
       case 
          when a = greatest(a,b,c,d) then 'a' 
          when b = greatest(a,b,c,d) then 'b' 
          when c = greatest(a,b,c,d) then 'c' 
          when d = greatest(a,b,c,d) then 'd' 
       end 
from test;

Note that the above will not work correctly if any of the columns contains a null value because greatest() will return null then. If you need to handle that, you need to use e.g.:

when a = greatest(coalesce(a,0),coalesce(b,0),coalesce(c,0),coalesce(d,0)) then 'a'

When two columns have the same highest value this would display the "first" one. So for (2,2,4,4) it would return c

Related Question