Cannot drop table users because other objects depend on it

Question

I want to drop my tables in my database. But, when I use, for example, DROP TABLE if exists users; I receive this message:

cannot drop table users because other objects depend on it

I found the solution is to drop all tables. But, anyway, how to solve this problem without total data removal?

Answer 1

Use the cascade option:

DROP TABLE if exists users cascade; 

this will drop any foreign key that is referencing the users table or any view using it.

It will not drop other tables (or delete rows from them).

Answer 2

If it was really necessary to drop that specific table with or without recreating it, then first find the object(s) that depends on it.

CREATE OR REPLACE VIEW admin.v_view_dependency AS  SELECT DISTINCT srcobj.oid AS src_oid   , srcnsp.nspname AS src_schemaname   , srcobj.relname AS src_objectname   , tgtobj.oid AS dependent_viewoid   , tgtnsp.nspname AS dependant_schemaname   , tgtobj.relname AS dependant_objectname FROM pg_class srcobj   JOIN pg_depend srcdep ON srcobj.oid = srcdep.refobjid   JOIN pg_depend tgtdep ON srcdep.objid = tgtdep.objid   JOIN pg_class tgtobj ON tgtdep.refobjid = tgtobj.oid AND srcobj.oid <> tgtobj.oid   LEFT JOIN pg_namespace srcnsp ON srcobj.relnamespace = srcnsp.oid   LEFT JOIN pg_namespace tgtnsp ON tgtobj.relnamespace = tgtnsp.oid WHERE tgtdep.deptype = 'i'::"char" AND tgtobj.relkind = 'v'::"char"; 

Then,

select top 99 * from admin.v_view_dependency where src_objectname like '%the_table_name_it_complaint_about%'; 

The result set will show you the dependant object in the field "dependant_objectname".