Блог

У нас есть горизонтальное представление данных, подобное этому:

 |id|col1|col2|col3|col4|col5|
|01|1111|2222|3333|4444|5555|
|02|1234|5678|9012|3456|7890|
...
  

Я хотел бы преобразовать его в это:

 |id|col|col_data|
|01|  1|    1111|
|01|  2|    2222|
|01|  3|    3333|
|01|  4|    4444|
|01|  5|    5555|
|02|  1|    1234|
|02|  2|    5678|
|02|  3|    9012|
|02|  4|    3456|
|02|  5|    7890|
....
  

Я смог правильно перечислить |id|col| столбцы моей целевой таблицы, используя это:

 SELECT 
    id,
    unnest((
        select
            array_agg(split_part(column_name::text, 'l', 2)::numeric)
        FROM INFORMATION_SCHEMA.columns
        WHERE 
            TABLE_NAME = 'my_table' and 
            TABLE_SCHEMA = 'my_schema'
    )) AS "col"
FROM my_schema.my_table
group by "col" id
ORDER BY id, "col";
  

Но я не знаю, как получить данные из исходной таблицы в нужном формате.

К вашему СВЕДЕНИЮ:

• У меня нет прав суперпользователя.
• Фактическая таблица содержит 26 столбцов

Одним из способов является боковое соединение, если все столбцы имеют один и тот же тип данных:

 select t.id, up.*
from the_table t
  cross join lateral (
    values (1, col1), (2, col2), (3, col3), (4, col4), (5, col5)
  ) as up(col, col_data);
  

Если вас не волнует, что все преобразуется в текст (без сохранения исходных типов данных), вы можете использовать JSON для отключения столбцов:

 select t.id, 
       up.col, 
       up.col_data
from the_table t
  cross join jsonb_each_text(to_jsonb(t) - 'id') as up(col, col_data)