Question 114945 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is there a way to use functions that can take multiple columns as input (e.g. `GREATEST`) without typing out every column name?

I have a table with a couple of 100 columns on which I want to use the GREATEST function. How can I prevent putting each and every column name into the query?

The columns on which I want to use GREATEST have a common prefix in case this might help.

That’s how I am doing it now.

CREATE TEMP TABLE foo (id int, cc_a int, cc_b int, cc_c int);
INSERT INTO foo VALUES (3, '1', '2', '3'), (4, '15', '4', '100');

SELECT id, GREATEST(cc_a, cc_b, cc_c) FROM foo
Tags:

2

Answers


  1. 0

    You can turn a row into a list of values using e.g. JSON functions:

    SELECT id, 
       (select max(x.val::int)
       from jsonb_each(to_jsonb(f) - 'id') as x(col, val))
from foo f

    Online example

    But I do agree that this smells like a bad design.

    Login or Signup to reply.
  2. 0

    Since you want to operate on a large number of values per id, it’s time to bite the bullet and create an UNPIVOTed view of your table.

    Con : you have to type the hundred values
    Pro : you only have to it once in your code base

    I know you are working on PostgreSQL, so there are solutions such as the hstore based solution which according to the answerer, may work on an arbitrary large number of columns.

    if you can’t use the hstore extension – it’s not installed on dbfiddle.com. then you can fall back on LATERAL JOIN, inspired by https://blog.sql-workbench.eu/post/unpivot-with-postgres/. I tested a solution in this fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search