Single INSERT with multiple rows from multiple SELECT statements

Question

I have multiple (> 100) PostgreSQL INSERT statements which all looks like this one, but with different selections:

INSERT INTO schema.table (geom, name, label, flag, type)
SELECT (geom, <complicated string concatenation from multiple fields>, the_label, TRUE, type_id)
FROM abc.xzy a WHERE a.changelog = 1
ORDER BY a.num;

The SQL query may change but its output is done to fit the INSERT requirements, of course.

I'd like to do it all with a single INSERT. Maybe using the VALUES clause like:

INSERT INTO ... VALUES
  (val1,val2,val3,val4,...)
, (val11,val12,val13,val14,...)

This is shown to work here, with simple 'static' values (not from SELECT statements).

But it's not working with SELECT to fetch the values for the INSERT:

INSERT INTO schema.table (geom, name, label, flag, type)
VALUES
  (
    SELECT (geom, <complicated string concatenation from multiple fields>, the_label, TRUE, type_id)
    FROM abc.xzy a WHERE a.changelog = 1
    ORDER BY a.num
  ),
  (
    SELECT (geom, <complicated string concatenation from multiple fields>, a_label, FALSE, type_id)
    FROM def.uvt a WHERE a.typedf
    ORDER BY a.idx
  ),
...
);

I get an error: subquery must return only one column on the very first opening parenthesis, just before the very first SELECT.

How could I fix that, if possible?

PG version is >= 9.6.

Erwin Brandstetter · Accepted Answer · 2021-10-01 15:40:42Z

You attempt indicates confusion between values (fields) and rows (records).

You don't need a VALUES expression. Just use the SELECT statements directly. Chain multiple SELECT with UNION ALL:

INSERT INTO schema.table (geom, name, label, flag, type)
(
SELECT geom, <complicated string concatenation from multiple fields>, the_label, TRUE, type_id
FROM   abc.xzy a WHERE a.changelog = 1
ORDER  BY a.num
)
UNION ALL
(
SELECT geom, <complicated string concatenation from multiple fields>, a_label, FALSE, type_id
FROM   def.uvt a WHERE a.typedf
ORDER  BY a.idx
);

The extra parentheses are only needed to allow ORDER BY per SELECT. But that's only useful if the physical order of rows is relevant. (For performance optimization.) The order of inserted rows has no other significance and can change later with write operations. So you probably just want:

INSERT INTO schema.table (geom, name, label, flag, type)
SELECT geom, <complicated string concatenation from multiple fields>, the_label, TRUE, type_id
FROM   abc.xzy a WHERE a.changelog = 1
UNION ALL
SELECT geom, <complicated string concatenation from multiple fields>, a_label, FALSE, type_id
FROM   def.uvt a WHERE a.typedf;

Collectives™ on Stack Overflow

Single INSERT with multiple rows from multiple SELECT statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related