How to change SRID in Postgis

Question

I am very green in both Postgis and Postgresql. I wanted to alter the geom column in my table in order to change the SRID but I receive this error:

cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view vw_select_location depends on column "geom"
CONTEXT:  SQL statement "ALTER TABLE public.ponds_data ALTER COLUMN geom TYPE  geometry(PointZM, 4210) USING ST_SetSRID(geom,4210);"
PL/pgSQL function updategeometrysrid(character varying,character varying,character varying,character varying,integer) line 81 at EXECUTE statement
SQL statement "SELECT UpdateGeometrySRID('','',$1,$2,$3)"
PL/pgSQL function updategeometrysrid(character varying,character varying,integer) line 5 at SQL statement.

Please help. I wants to change the reference coordinate system to arc 1960.

Tom-db Tom-db · Accepted Answer · 2015-11-30T06:35:01

Read the error message carefully: you cannot change the column geom on table ponds_data because the view vw_select_location depends on it.

Solution: first save the view definition in a file. You can find definition of the view in pgadmin or in psql. In psql use the command

\d+ vw_select_location

Then drop the view, change the column and recreate the view with the sql statement you saved before.

How to change SRID in Postgis

1 Answers