CODEWITHSUNDEEP
sqlpostgresql
user122222
user122222

Reputation: 2449

Use declared table type variable for inserts postgres

I have table from which I select ids:

SELECT  Ids
FROM    C 
WHERE  C.Type = 6

Then I have a lot of inserts, that use the same ids:

    INSERT INTO D (x, y, z)
    SELECT 20 as x, 2.90 as y, z FROM C WHERE Ids in (1, 2, 3, 4, 5, 6);

    INSERT INTO D (x, y, z)
    SELECT 11 as x, 33 as y, z FROM C WHERE Ids in (1, 2, 3, 4, 5, 6);

    INSERT INTO D (x, y, z)
    SELECT 12 as x, 2.11 as y, z FROM C WHERE Ids in (1, 2, 3, 4, 5, 6);

since these Ids are the same, I want to somehow optimize it/make it more readable so that I wouldn't copy paste all these ids (there are several hundred of them). For this reason I would like to declare table type variable, insert these ids into that table and when creating INSERTS select these ids from that table. However I'm noob here and don't really get how I should do that and all the examples I find seem complicated. Could someone give a helping hand here?

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

How about doing all the inserts in one statement?

INSERT INTO D (x, y, z)
    SELECT v.x, v.y, c.z
    FROM C CROSS JOIN
         (VALUES (20, 2.9), (11, 33), (12, 2.11)
         ) v(x, y)
    WHERE Ids in (1, 2, 3, 4, 5, 6);

You could also define the ids in a CTE, and then do all the inserts in one statement:

with ids(id) as (
      values (1, 2, 3, 4, 5, 6)
     )
insert into d (x, y, z)
    select 20, 2.9, c.z
    from c
    where c.id in (select id from ids)
    union all
    select 11, 33, c.z
    from c
    where c.id in (select id from ids)
    union all
    . . . ;

Upvotes: 1

Related Questions