Fixing PostGIS Column Z Dimension Issue When Geometry Does Not

3dpostgis

I am able to create a geomtry column for 3d multiline using this:

    CREATE TABLE trajectories
(
  gid serial NOT NULL,
  vehid character varying(10),
  state integer,
  geom geometry(MultiLineStringZ),
  CONSTRAINT trajectories_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE trajectories
  OWNER TO postgres;

But when I tried to alter an existing table by modifying an existing geom column or adding a new one, I am getting he following error:

ERROR: Column has Z dimension but geometry does not

****** Error ******

ERROR: Column has Z dimension but geometry does not SQL state: 22023
ERROR: Column has Z dimension but geometry does not

ALTER TABLE cl ALTER COLUMN geom TYPE geometry(MultiLineStringZ)
  1. How do I modify and existing geometry column to geometry(MultiLineStringZ)?
  2. How do I add new geometry(MultiLineStringZ) column to an existing table?

Best Answer

When there is no implicit cast between two datatypes you need to state it explicitly. In your case ST_Force3D (or ST_Force_3D for PostGIS versions earlier than 2.1) should do the trick:

ALTER TABLE cl 
  ALTER COLUMN geom TYPE geometry(MultiLineStringZ)
    USING ST_Force_3D(geom);
Related Question