CODEWITHSUNDEEP
sqlpostgresql
n1000
n1000

Reputation: 5314

INSERT multiple rows from SELECT in Postgresql

As described in the PostgreSQL manual one can add multiple rows in a single INSERT statement:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('6120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

Also a nested SELECT query like this works:

INSERT INTO films (code, title, did, date_prod, kind)
     SELECT table.code, 'Tampopo', 110, '1985-02-10', 'Comedy'
     FROM other_table AS table2;

However, I can't seem to figure out a way to do both at the same time:

INSERT INTO films (code, title, did, date_prod, kind) 
SELECT
    (table.code + 100, 'abc', NULL, t2.date_prod, t2.kind),
    (table.code + 200, 'xyz', NULL, t2.date_prod, t2.kind)
FROM other_table AS t2;

If other_table contained only (61717 | 'Tampopo' | NULL | '1985-02-10' | 'Comedy'), the result table would look like this:

 code | title |  did |   date_prod  |   kind
--------------------------------------------------
61817 | 'abc' | NULL | '1985-02-10' | 'Comedy'
61917 | 'xyz' | NULL | '1985-02-10' | 'Comedy'

This fiddle should explain it further...

Could you please point me to where I am going wrong. If possible, I would like to avoid multiple lookups in other_table AS t2 because it is a VIEW and it takes considerable time to construct.

Upvotes: 14

Views: 24100

Answers (3)

Zhenya
Zhenya

Reputation: 79

use construction

insert into table(column1)
((select uuid from table2))

Upvotes: -1

klin
klin

Reputation: 121494

Generally you can use select with union:

insert into films (code, title, did, date_prod, kind) 
    select t2.code + 100, t2.title, NULL::float, t2.date_prod, t2.kind
    from other_table AS t2
union
    select t2.code + 200, t2.title, NULL::float, t2.date_prod, t2.kind
    from other_table AS t2;

In the particular case you described, you can use the unnest (array []):

insert into films (code, title, did, date_prod, kind) 
    select 
        unnest(array[t2.code + 100, t2.code + 200]), 
        t2.title, NULL::float, t2.date_prod, t2.kind
    from other_table AS t2

Upvotes: 14

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

You can use INSERT INTO ... UNION ALL:

INSERT INTO films (code, title, did, date_prod, kind)
SELECT t2.code + 100, t2.title, t2.did, t2.date_prod, t2.kind
FROM other_table AS t2
UNION ALL
SELECT t2.code + 200, t2.title, t2.did, t2.date_prod, t2.kind
FROM other_table AS t2;

Upvotes: 6

Related Questions