Dropping a table from PostGIS database using pgAdmin III

Question

A table in my PostGIS database (PostgreSQL 9.3.5) had the wrong geometry-type, therefore I wanted to change the geometry type. I tried to do this by dropping the geometry column like this:

ALTER TABLE xxx.my_table DROP COLUMN geometry_column;

After which I would have added a new geometry column of the correct type. In hindsight, I should probably have used ALTER COLUMN, however this didn't occur to me at the time.

The query started to run, however it never finished, so I had to stop the execution. Following this, every time I click on the table from within pgAdmin, the program crashes. I have tried using DROP TABLE to delete the table, but the query just runs without finishing. I also tried deleting the entire schema from within pgAdmin, however this also causes a crash.

The program gets stuck when "retrieving details" on the table, so I suspect the table is corrupt in some way. We have a backup of the schema in a working state, however using the restore functionality in pgAdmin doest not seem to work either.

Is there anyway to "fix" the table or force delete it in any way?

• nickves: This sounds like a bug to me
• Chrisnizz: A bug in pgAdmin or PostGIS/PostgreSQL?
• Fjellrev: Try using psql instead to make the necessary changes.
• TurboGraphxBeige: Is the query still running? "SELECT * FROM pg_stat_activity"

Answer 1

If you're using PostgreSQL 9.3.5, you're likely using PostGIS 2.1/2.2. Being 2.x both of these should use a view for geometry_columns. It's hard to tell where the problem exists. I back @Fjellrev's suggestion though,

Try using psql instead to make the necessary changes.

Using psql, connect and run \d+ xxx.my_table if that works, pasted the result above.

Either way, DROP TABLE xxx.my_table; should certainly work in psql