PostGIS ST_Union using array of geometries

Question

I'm new to PostGIS and I have a set of layers that need to be merged and dissolved. I have no problem doing this for 2 input layers using ST_Union(geometry g1, geometry g2), but for more than 2 layers, using the ST_Union(geometry[] g1_array) approach I get no result because I fail to build the array correctly.

Here is a sample code:

CREATE TABLE test.test AS SELECT
ST_Union(
ARRAY[
  layer01.geom FROM test.layer01,
  layer02.geom FROM test.layer02,
  layer03.geom FROM test.layer03,
  layer04.geom FROM test.layer04,
  layer05.geom FROM test.layer05,
  layer06.geom FROM test.layer06,
  layer07.geom FROM test.layer07
])

I get this error message (the locale is german):

ERROR: FEHLER: Syntaxfehler bei "FROM"
LINE 4: FROM test.layer01
SQL state: 42601

I found out that the SQL state 42601 refers to a mix of plain and dynamic SQL, but I can't fix this.

What can I do?

Answer 1

Many thanks for your answer, but I still receive an ERROR 42601.

ERROR: FEHLER: Syntaxfehler bei "SELECT"
LINE 4: FROM test.layer01
SQL state: 42601

I have made a few attempts and finally this worked:

CREATE TABLE test.test AS SELECT
ST_Union(
ARRAY[
  (SELECT layer01.geom FROM test.layer01),
  (SELECT layer02.geom FROM test.layer02),
  (SELECT layer03.geom FROM test.layer03),
  (SELECT layer04.geom FROM test.layer04),
  (SELECT layer05.geom FROM test.layer05),
  (SELECT layer06.geom FROM test.layer06),
  (SELECT layer07.geom FROM test.layer07)
])

So this is the solution.

Answer 2

You need to build the complete query inside the array, the SELECT is missing

CREATE TABLE test.test AS SELECT
ST_Union(
ARRAY[
  SELECT layer01.geom FROM test.layer01,
  SELECT layer02.geom FROM test.layer02,
  SELECT layer03.geom FROM test.layer03,
  SELECT layer04.geom FROM test.layer04,
  SELECT layer05.geom FROM test.layer05,
  SELECT layer06.geom FROM test.layer06,
  SELECT layer07.geom FROM test.layer07
])