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 [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
- 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 of the table to drop.
Clause that indicates to automatically drop objects that depend on the table, such as views.
Clause that indicates not to drop the table if any objects depend on it. This action is the default.
Dropping a Table with No Dependencies
The following example creates and drops a table called FEEDBACK that has no dependencies:
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:
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:
Dropping a Table with a DependencyCopy
create table feedback(a int); create table buyers(a int); drop table feedback, buyers;
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:
Next, use the CREATE VIEW command to create a view called FEEDBACK_VIEW that relies on the table FEEDBACK:Copy
create table feedback(a int);
The following example drops the table FEEDBACK and also drops the view FEEDBACK_VIEW, because FEEDBACK_VIEW is dependent on the table FEEDBACK:Copy
create view feedback_view as select * from feedback;
Viewing the Dependencies for a TableCopy
drop table feedback cascade;
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:
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:
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:
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:
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:
drop table if exists feedback;