How do I delete (drop) tables?

You can drop one or more tables by using the DROP TABLE command, which can be executed from the Analyze page or any connected SQL client.

To drop a single table, enter DROP TABLE "S"."X", where S is the schema and X is the table name.

To drop several tables, enter DROP TABLE "S"."X", "S"."Y", "S"."Z", where X, Y, and Z are a comma-separated list of table names and S is their schema.

To generate a list of all tables that match some prefix you can run:
select distinct '"' + schemaname + '"."' + tablename + '"' from pg_tables where tablename like 'some_prefix%';

Get the List of Tables

You can execute a SQL query to get a list of tables that should be dropped.
For example, use the following WHERE clause to drop all tables with a specific prefix: WHERE tablename like 'prefix%'.

To get a list of all the tables in the public schema:

  1. Execute the following SQL query: SELECT 'DROP TABLE "' + schemaname + '"."' + tablename + '";' FROM pg_tables WHERE schemaname = 'public';
  2. Copy the results, not including the header.
  3. Paste the results and execute all the SQL commands (all can be run at once).

Use Case

The following instructions demonstrate how to drop multiple intercom tables.

  1. Run the following SQL query: SELECT 'DROP TABLE "' + schemaname + '"."' + tablename + '";' FROM pg_tables where schemaname = 'stitch_intercom';
  2. Copy the results, not including the header.
  3. For this set of tables, the commands will be:
    DROP TABLE "stitch_intercom"."_sdc_rejected";
    DROP TABLE "stitch_intercom"."admins";
    DROP TABLE "stitch_intercom"."admins__admin_ids";
    DROP TABLE "stitch_intercom"."admins__team_ids";
    DROP TABLE "stitch_intercom"."contacts";
    DROP TABLE "stitch_intercom"."contacts__social_profiles__social_profiles";
    DROP TABLE "stitch_intercom"."contacts__tags__tags";
    DROP TABLE "stitch_intercom"."conversations";
    DROP TABLE "stitch_intercom"."conversations__conversation_message__attachments";
    DROP TABLE "stitch_intercom"."conversations__conversation_parts";
    DROP TABLE "stitch_intercom"."conversations__conversation_parts__attachments";
    DROP TABLE "stitch_intercom"."conversations__customers";
    DROP TABLE "stitch_intercom"."conversations__tags__tags";
    DROP TABLE "stitch_intercom"."segments";
    DROP TABLE "stitch_intercom"."tags";
    DROP TABLE "stitch_intercom"."users";
    DROP TABLE "stitch_intercom"."users__segments__segments";
    DROP TABLE "stitch_intercom"."users__social_profiles__social_profiles";
    DROP TABLE "stitch_intercom"."users__tags__tags";
    

Note, you will get an error if there are dependencies on these tables, such as views you may have built.
If the table that you are trying to drop has views that are dependent on it you can run the following to identify these views, (just change the table name and schema name):

SELECT DISTINCT c_p.oid AS tbloid,
       n_p.nspname AS dependee_schema,
       c_p.relname AS dependee,
       n_c.nspname AS dependent_schema,
       c_c.relname AS dependent,
       c_c.oid AS viewoid
FROM pg_class c_p
  JOIN pg_depend d_p ON c_p.relfilenode = d_p.refobjid
  JOIN pg_depend d_c ON d_p.objid = d_c.objid
  JOIN pg_class c_c ON d_c.refobjid = c_c.relfilenode
  LEFT JOIN pg_namespace n_p ON c_p.relnamespace = n_p.oid
  LEFT JOIN pg_namespace n_c ON c_c.relnamespace = n_c.oid
WHERE dependee = 'table name'
AND   dependee_schema = 'schema name'
AND   dependee_schema + dependee <> dependent_schema + dependent;
1 Like