skip to Main Content

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.

2

Answers


  1. 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. 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