PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert into table based on condition in another column

I have the following two tables

table c

id type
1 B1
2 B2
3 B3

table s

id s_id s_type
1 2 C1
2 2 C2
3 3 C2

Now I want to insert new rows in the table for all rows where B is either B1 or B2. I did this, by using the following statements:

for id in (select id from table where type = 'B1')
        loop
            begin
                next_id = get_next_id('id_seq', next_id, 1000);
                insert into s(id, s_id, s_type)
                values (next_id, id, 'C2');
            end;
        end loop;

for id in (select id from table where type = 'B2')
        loop
            begin
                next_id = get_next_id('id_seq', next_id, 1000);
                insert into s(id, s_id, s_type)
                values (next_id, id, 'C1');
            end;
        end loop;

Now I’m wondering if I would be possible to do that in 1 loop and if so, if it even makes sense.

Tags:

2

Answers


  1. 0 
    INSERT INTO table(id, A, B)
SELECT 
    get_next_id('id_seq', next_id, 1000),
    id, 
    CASE 
        WHEN B = 'B1' THEN 'A2'
        WHEN B = 'B2' THEN 'A3'
    END
FROM 
    table
WHERE 
    B IN ('B1', 'B2');
    Login or Signup to reply.
  2. 0

    You can union a select of each of the input c.types and feed that to a single insert. No PL/pgSQL, no loops, just a native, set-based operation:
    demo at db<>fiddle

    insert into s
select get_next_id('id_seq', next_id, 1000)
     , id
     , 'C2'
from c
where type='B1'
union all
select get_next_id('id_seq', next_id, 1000)
     , id
     , 'C1'
from c
where type='B2'

    Or, as already suggested by @Jan Suchanek, you can run a single select and switch between the two target s.s_types based on the input c.type using a simple case.

    insert into s
select get_next_id('id_seq', 'next_id', 1000)
     , id
     , case type when 'B1' then 'C2'
                 when 'B2' then 'C1' end
from c
where type=any(array['B1', 'B2']);

    Without an index on the type column, these take about .7s on a 700k sample data set, which drops to about .5s with an index. There’s not much difference beyond that – one does two simple scans, the other one with a bit more work on top due to the conditional, both ending up taking about the same amount of time/effort.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search