PostGIS Column has Z dimension but geometry does not

Question

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?

Answer 1

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);

Answer 2

The answer given by Jakub is correct but if you're using QGIS for editing, you may also need to update your project to reflect the new geometry type.

For example, if you've changed an existing table shapes from MultiPolygon to MultiPolygonZ, open your project file (filename.qgs) in a text editor and change the table's geometry type for each datasource it appears in. That is, change

<datasource>service='geodatabase' key='gid' srid=4326 type=MultiPolygon table="public"."shapes" (geom) sql=</datasource>

to

<datasource>service='geodatabase' key='gid' srid=4326 type=MultiPolygonZ table="public"."shapes" (geom) sql=</datasource>

Once this is done reload your project in QGIS and you should be good to go.