Insert column name as value

Question

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

That could still be done in a view. Which DBMS are you using? — user1822
– user1822, Commented Jun 1, 2016 at 10:01
What should the column contain if two or more columns have the same value? You would need an update to change the value later, not an insert. But what is the real problem you are trying to solve here? This sounds pretty weird and maybe the underlying problem should be solved in a different way. — user1822
– user1822, Commented Jun 1, 2016 at 10:04

score 2 · Accepted Answer · 2016-06-01 10:54:35Z

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

[This answer correctly addresses an earlier version of the question. Note that a,b,b,c should be a,b,c,d.] — SabreWolfy
– SabreWolfy, Commented Jun 1, 2016 at 10:45

Stack Exchange Network

Insert column name as value

1 Answer 1

Hot Network Questions

Insert column name as value

1 Answer 1

Related

Hot Network Questions