INSERT multiple rows from SELECT in Postgresql

Question

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.

table.code + 100 is also not going to work. code seems to be a character column, you can't use + with strings. Plus: what is the expected result of 'HG120' + 100 - that doesn't make sense. — user330315
– user330315, Commented Oct 18, 2015 at 17:58
@a_horse_with_no_name Uff. Sorry. Tried to make things clearer... It should have been an INT. — n1000
– n1000, Commented Oct 18, 2015 at 18:03

klin · Accepted Answer · 2015-10-18 19:20:11Z

15

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

edited Oct 18, 2015 at 19:20

answered Oct 18, 2015 at 18:01

klin

123k15 gold badges240 silver badges262 bronze badges

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

5 Comments

n1000 Over a year ago

The same problem applies to your solution :( Try uncommenting: sqlfiddle.com/#!15/4884e/3/0

klin Over a year ago

Simple cast. See the edited answer. (You should give the table definition earlier in the question).

n1000 Over a year ago

Ok last time I bother - promised :) Your second solution is interesting because with UNION it seems that the other_table VIEW is constructed twice, which is quite expensive in my case. Could the array be extended to the other values or the entire row?

klin Over a year ago

There is no general rule, but the use of unnest(array[]) for several columns is a bit more complicated. However, you can always check what the result the select gives before using it in the insert. Or ask a new question ;)

BeshEater Over a year ago

@n1000 You could use the WITH statement in order to extract a common table from the query, So the query will start like WITH other_table AS (....)

Lukasz Szozda · Accepted Answer · 2015-10-18 18:06:20Z

6

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;

edited Oct 18, 2015 at 18:06

answered Oct 18, 2015 at 17:54

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

9 Comments

Lukasz Szozda Over a year ago

@n1000 Updated, anyway the UNION ALL will do the job

n1000 Over a year ago

will this cause the VIEW other_table to be constructed twice?

Lukasz Szozda Over a year ago

Probably not, but to be sure check execution plan

Lukasz Szozda Over a year ago

@n1000 Could you decide on one version? Now the title is hardcoded value abc and xyx or how it depends on table2?

Will Hogan Over a year ago

Since you're just inserting NULL into did you can exclude it from the insert and select: sqlfiddle.com/#!15/eeee1/1/0

|

Zhenya · Accepted Answer · 2022-06-30 04:28:11Z

-1

use construction

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

answered Jun 30, 2022 at 4:28

Zhenya

796 bronze badges

Collectives™ on Stack Overflow

INSERT multiple rows from SELECT in Postgresql

3 Answers 3

5 Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related