Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

DROP TABLE

Removes a table from a database. Only the owner of the table, the schema owner, or a superuser can drop a table.

If you are trying to empty a table of rows, without removing the table, use the DELETE or TRUNCATE command.

DROP TABLE removes constraints that exist on the target table. Multiple tables can be removed with a single DROP TABLE command.

DROP TABLE with an external table can't be used inside a transaction (BEGIN … END).

Syntax

Copy
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Parameters

IF EXISTS

Clause that indicates that if the specified table doesn’t exist, the command should make no changes and return a message that the table doesn't exist, rather than terminating with an error.

This clause is useful when scripting, so the script doesn’t fail if DROP TABLE runs against a nonexistent table.

name

Name of the table to drop.

CASCADE

Clause that indicates to automatically drop objects that depend on the table, such as views.

RESTRICT

Clause that indicates not to drop the table if any objects depend on it. This action is the default.

Examples

Dropping a Table with No Dependencies

The following example creates and drops a table called FEEDBACK that has no dependencies:

Copy
create table feedback(a int); drop table feedback;

If a table contains any columns that are references to other tables, Amazon Redshift displays a message such as the following, advising you to use the CASCADE option to also drop dependent objects:

Copy
ERROR: cannot drop table category because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too.

Dropping Two Tables Simultaneously

The following command set creates a FEEDBACK table and a BUYERS table and then drops both tables with a single command:

Copy
create table feedback(a int); create table buyers(a int); drop table feedback, buyers;

Dropping a Table with a Dependency

The following steps show how to drop a table called FEEDBACK using the CASCADE switch.

First, create a simple table called FEEDBACK using the CREATE TABLE command:

Copy
create table feedback(a int);

Next, use the CREATE VIEW command to create a view called FEEDBACK_VIEW that relies on the table FEEDBACK:

Copy
create view feedback_view as select * from feedback;

The following example drops the table FEEDBACK and also drops the view FEEDBACK_VIEW, because FEEDBACK_VIEW is dependent on the table FEEDBACK:

Copy
drop table feedback cascade;

Viewing the Dependencies for a Table

You can create a view that holds the dependency information for all of the tables in a database. Before dropping a given table, query this view to determine if the table has dependencies.

Type the following command to create a FIND_DEPEND view, which joins dependencies with object references:

Copy
create view find_depend as select distinct c_p.oid as tbloid, n_p.nspname as schemaname, c_p.relname as name, n_c.nspname as refbyschemaname, c_c.relname as refbyname, c_c.oid as viewoid from pg_catalog.pg_class c_p join pg_catalog.pg_depend d_p on c_p.relfilenode = d_p.refobjid join pg_catalog.pg_depend d_c on d_p.objid = d_c.objid join pg_catalog.pg_class c_c on d_c.refobjid = c_c.relfilenode left outer join pg_namespace n_p on c_p.relnamespace = n_p.oid left outer join pg_namespace n_c on c_c.relnamespace = n_c.oid where d_c.deptype = 'i'::"char" and c_c.relkind = 'v'::"char";

Now create a SALES_VIEW from the SALES table:

Copy
create view sales_view as select * from sales;

Now query the FIND_DEPEND view to view dependencies in the database. Limit the scope of the query to the PUBLIC schema, as shown in the following code:

Copy
select * from find_depend where refbyschemaname='public' order by name;

This query returns the following dependencies, showing that the SALES_VIEW view is also dropped by using the CASCADE option when dropping the SALES table:

Copy
tbloid | schemaname | name | viewoid | refbyschemaname | refbyname --------+------------+-------------+---------+-----------------+------------- 100241 | public | find_depend | 100241 | public | find_depend 100203 | public | sales | 100245 | public | sales_view 100245 | public | sales_view | 100245 | public | sales_view (3 rows)

Dropping a Table Using IF EXISTS

The following example either drops the FEEDBACK table if it exists, or does nothing and returns a message if it does not:

Copy
drop table if exists feedback;

On this page: