Change a column participating in a view or rule

Question

I am using a tool called giswater to model our water sewer and storm networks. The tools builds a database from a few simple inputs in a gui one of which is the SRID. the geometry columns are all xy and I want to make them all ZM aware. When I run the alter table command to update the geometry I get an error "Cannot alter column participating in a view or rule" Anyway to force this change and ignore the error? I tried changing the view to not reference the column and I tried adding a new geometry column to switch the view to temporarily while I make the change. Apparently I cannot drop a column in a view or change it to another column. I also tried writing the schema to SQL then edited the sql lines for linestring and point to linestringzm and pointzm and using psql to run the file to update the schema; all I get is access denied using "psql -U postgres -d utility -1 -f \i Z:......\xyz_test.sql" Also tried pg_restore. Anyway to just force the change using pg_admin4? or other suggestions?

Answer 1

I am not familiar with the tool you are using but I would suggest checking all the objects (views and rules) referencing your table by using the below command.

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%'

Then backing up these object and dropping them completely before attempting to alter the table.