Skip to main content
The DROP TABLE statement removes a table and all its indexes from a database.

​
Required privileges

You must have the DROP privilege on the specified table(s). If CASCADE is used, you must have the privileges required to drop each dependent object as well.

​
Syntax

DROP TABLE [IF EXISTS] table_name [, ...] [CASCADE | RESTRICT]

​
Parameters

​
IF EXISTS
keyword
Drop the table if it exists. If it does not exist, do not return an error.
​
table_name
identifier
A comma-separated list of table names to drop. To find table names, use SHOW TABLES.
​
CASCADE
keyword
Drop all objects (such as constraints and views) that depend on the table.CASCADE does not list objects it drops, so should be used cautiously.
​
RESTRICT
keyword
(Default) Do not drop the table if any objects (such as constraints and views) depend on it.

​
Examples

​
Remove a table (no dependencies)

In this example, other objects do not depend on the table being dropped. 
SHOW TABLES FROM movr;

  schema_name |         table_name         | type
--------------+----------------------------+-------
  public      | promo_codes                | table
  public      | rides                      | table
  public      | user_promo_codes           | table
  public      | users                      | table
  public      | vehicle_location_histories | table
  public      | vehicles                   | table

DROP TABLE promo_codes;

DROP TABLE

SHOW TABLES FROM movr;

  schema_name |         table_name         | type
--------------+----------------------------+-------
  public      | rides                      | table
  public      | user_promo_codes           | table
  public      | users                      | table
  public      | vehicle_location_histories | table
  public      | vehicles                   | table

​
Remove a table and dependent objects with CASCADE

In this example, a foreign key from a different table references the table being dropped. Therefore, it’s only possible to drop the table while simultaneously dropping the dependent foreign key constraint using CASCADE.
CASCADE drops all dependent objects without listing them, which can lead to inadvertent and difficult-to-recover losses. To avoid potential harm, we recommend dropping objects individually in most cases.
SHOW TABLES FROM movr;

  schema_name |         table_name         | type
--------------+----------------------------+-------
  public      | rides                      | table
  public      | user_promo_codes           | table
  public      | users                      | table
  public      | vehicle_location_histories | table
  public      | vehicles                   | table

DROP TABLE users;

pq: "users" is referenced by foreign key from table "vehicles"
To see how users is referenced from vehicles, use the SHOW CREATE statement: 
SHOW CREATE TABLE vehicles;

  table_name |                                         create_statement
-------------+---------------------------------------------------------------------------------------------------
  vehicles   | CREATE TABLE public.vehicles (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     type VARCHAR NULL,
             |     owner_id UUID NULL,
             |     ...
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES public.users(city, id),
             |     ...
             | )
Drop the table with CASCADE: 
DROP TABLE users CASCADE;

DROP TABLE
Verify that the foreign key constraint has been removed: 
SHOW CREATE TABLE vehicles;
The foreign key constraint fk_city_ref_users is no longer present.

​
See also

  • ALTER TABLE
  • CREATE TABLE
  • SHOW TABLES
  • SHOW COLUMNS
  • DROP INDEX
  • DROP VIEW

Build docs developers (and LLMs) love

Get started for freeTalk to us