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

Question

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

If I followed you correctly, shouldn't second row in table2 be q2 20 25 15 instead of q2 20 15 15? — GMB
– GMB, Commented Nov 19, 2019 at 8:31

GMB · Accepted Answer · 2019-11-19 08:50:30Z

1

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

answered Nov 19, 2019 at 8:50

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pseudalitos Over a year ago

thx for your help, i will try this. but what does rnb = 1... mean?

GMB Over a year ago

@Pseudalitos: rnb is the alias for one of the ranks affected in the inner query. You can run the subquery independantly to see what it returns.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related