DROP TABLE

Description

Deletes a table.

Precautions

After DROP TABLE deletes the table, the indexes depending on the table are deleted, and the functions and stored procedures that need to use this table cannot be executed. Deleting a partitioned table also deletes all partitions in the table.
The owner of a table, the owner of the schema of the table, users granted with the DROP permission on the table, or users granted with the DROP ANY TABLE permission can delete the specified table. When separation of duties is disabled, a system administrator has the permission to delete the specified table by default.
When DROP TABLE is executed, if the table to be deleted references another table as a foreign key table, triggers on the referenced table will be deleted in cascading mode. In this case, an eight-level lock needs to be added to the referenced table, which may cause service congestion.

Syntax

DROP TABLE [ IF EXISTS ] 
    { [schema.]table_name } [, ...] [ CASCADE | RESTRICT ] [ PURGE ];

Parameters

IF EXISTS
Reports a notice instead of an error if the specified table does not exist.
schema
Specifies the schema name.
table_name
Specifies the name of the table to be deleted.
CASCADE | RESTRICT
- CASCADE: allows to cascadingly delete the objects (such as views) that depend on the table.
- RESTRICT: refuses to delete the table if any objects depend on it. This is the default action.
PURGE
Specifies that even if the recycle bin function is enabled, the table is physically dropped instead of being moved to the recycle bin when you use DROP TABLE to delete tables.

Examples

-- Create the test table.
gaussdb=# CREATE TABLE test(c1 int, c2 int);
-- Drop the test table.
gaussdb=# DROP TABLE IF EXISTS test;

-- Create the test1 table.
gaussdb=# CREATE TABLE test1(c1 int, c2 int);
-- Create the v_test1 view.
gaussdb=# CREATE VIEW v_test1 AS SELECT * FROM test1 WHERE c1 < 20;
-- An error is reported when the table is deleted.
gaussdb=# DROP TABLE test1;
ERROR:  cannot drop table test1 because other objects depend on it
DETAIL:  view v_test1 depends on table test1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

-- Use the CASCADE parameter to drop the test1 table, which will automatically delete the views.
gaussdb=# DROP TABLE test1 CASCADE;
NOTICE:  drop cascades to view v_test1
DROP TABLE