CODEWITHSUNDEEP
postgresql
Pseudalitos
Pseudalitos

Reputation: 45

PostgreSQL Insert Values in a Table on a Select and different Conditions

I have a table1 and want to insert values into a table2 in postgres. Table1 is looking like this:

Table1:
    z  a b    c
    q1 x 10   5
    q1 y null 20
    q1 y null null
    q2 x 20   10
    q2 y null 15
    q2 y null 25

Values of Table1 should be inserted in a table2 like this:

Table2:
    z  b  c1 c2
    q1 10 20 null
    q2 20 15 25

Thus differnt values in table1.z should be one row in table 2. (table2.b = table1.b WHERE table1.a = x) (table2.c = table1.c WHERE table1.a = y)

I tried my best to keep it understandable

Upvotes: 0

Views: 46

Answers (1)

GMB
GMB

Reputation: 222482

This is a bit akward but it can be done. You would need to rank the records in an inner query, and then do conditional aggregation in the outer query:

insert into table2(z, b, c1, c2)
select 
    z,
    max(case when a = 'x' and rnb = 1 then b end),
    max(case when a = 'y' and rnc = 1 then c end),
    max(case when a = 'y' and rnc = 2 then c end)
from (
    select
        t.*,
        row_number() over(partition by z, a order by b) rnb,
        row_number() over(partition by z, a order by c) rnc
    from table1 t
) t
group by z

Demo on DB Fiddle:

z  |  b | c1 |   c2
:- | -: | -: | ---:
q1 | 10 | 20 | null
q2 | 20 | 15 |   25

Upvotes: 1

Related Questions