Postgres - Merging grouped rows into one row for insert

user_16936787 0
Viewed 28

I have some data from a query and the shape pretty much looks like this:

| Id | category | value |
|----|----------|-------|
| 1  | 'a'      |  2    |
| 1  | 'b'      |  5    |
| 2  | 'a'      |  3    |
| 2  | 'b'      |  4    |

I'm wanting to group that data and insert it into a table of the following structure

| Id | category_a_value | category_b_value|
|----|------------------|-----------------|
|  1 |       2          |          5      |
| 2  |       3          |          4      |

Is there a nice way to achieve this in Postgres? I couldn't figure out to group the data how I wanted so eventually I tried a INSERT INTO and on conflict approach selecting from the orginial query but this failed because you can't affect a row multiple times.

Thanks in advance

postgresql sql
1 Answers

You can use conditional aggregation, which in Postgres uses filter:

select id,
       max(value) filter (where category = 'a') as category_a_value,
       max(value) filter (where category = 'b') as category_b_value
from t
group by id;

You can then use insert . . . select to insert the results into an existing table.

user_11440350
Related