How do I cast a geometry type for mixed geometries (polygons & multipolygons) in 4326? `::geometry(4326) as shape` doesn't work

Question

I'd like to update the geometry definition to include a specific SRID

CREATE MATERIALIZED VIEW published.ql_cmd_cruise_layer_web
TABLESPACE pg_default
AS SELECT ql_cmd_cruise_layer_web.cruise_id,
    ql_cmd_cruise_layer_web.cruise,
    ql_cmd_cruise_layer_web.shape_wgs84::geometry(4326) AS shape_wgs84,
    ql_cmd_cruise_layer_web.cruise_data_url
   FROM oracle_fdw.ql_cmd_cruise_layer_web
WITH DATA;

The geom column holds both polygons and multipolygons, so I can't do ::geometry(Poygon,4326) which has worked on other, simpler tables.

Using ::geometry(4326) results in SQL Error [22023]: ERROR: Invalid geometry type modifier: 4326 Position: 1094

Answer 1

You must give a geometry type. Since you have mixed content, you can use the generic geometry type during the cast.

ql_cmd_cruise_layer_web.shape_wgs84::geometry(geometry,4326) AS shape_wgs84,