PostGIS query losing geometry column information

Question

Using PostGIS I want to collect all attributes into one polygon. The old table looks like this:

Column   | Type
---------+------
xyz_cols | `xyz`
geometry | geometry(MultipolgyonZ, 2193)

Performing the query:

CREATE TABLE new_table AS 
SELECT
  1::INT as id,
  ST_Collect(geom)
FROM
  old_table

which works but I lose all geometry information and the output table is like this:

Column   | Type
---------+------
id       | INT
geometry | geometry

Even if I wrap the ST_Collect in a ST_Multi() I lose the geometry information.

Answer 1

You've only lost the geometry's typmod. This is analogous to

select 1::numeric(3,2) AS num1, 1::numeric(3,2) / 1 AS num2;
 num1 |          num2
------+------------------------
 1.00 | 1.00000000000000000000

where the scale/precision typmod for numeric is undefined from the maths operation.

If you want to retain/enforce the typmod for the result, just use it with a cast, and pick out only MultiPolygons with ST_CollectionExtract:

CREATE TABLE new_table AS 
SELECT
  1::INT as id,
  ST_CollectionExtract(ST_Collect(geom), 3)::geometry(MultiPolygonZ,2193)
FROM
  old_table

Note that other geometry types, if present, will be MULTIPOLYGON Z EMPTY.