0

I have. Key-Value table of two columns. I want to get keys as column names and values in individual columns (named as were keys named before). How to do such thing in Postgres ?

Is it possible to get more than one Row for a single key if there were multiple values with same key in original table?

1

1 Answer 1

1

This is a pivot type query and this is usually done using filtered aggregation:

select string_agg(value, ',') filter (where key = 'key1') as key_1,
       string_agg(value, ',') filter (where key = 'key2') as key_2,
       string_agg(value, ',') filter (where key = 'key3') as key_3
from the_table;

It's not possible to make this dynamic.

A fundamental restriction of SQL is that the number, names and types of all columns of a query must be known before the query is executed.


Depending on how you process the result, aggregating everything into a JSON object might be an alternative:

select jsonb_object_agg(key, vals)
from (
   select key, jsonb_agg(value) as vals
   from the_table
   group by key
) t
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.