How to DROP TABLE in PostgreSQL [With Different Examples]

In this PostgreSQL tutorial, we will discuss, about PostgreSQL DROP TABLE statement to remove existing table(s) from the current database and will cover the following topic:

• PostgreSQL DROP TABLE
• PostgreSQL DROP Multiple Tables
• PostgreSQL DROP TABLE CASCADE
• PostgreSQL DROP TABLE CASCADE sequence
• PostgreSQL DROP TABLE IF EXISTS
• PostgreSQL DROP TABLE CASCADE IF EXISTS
• PostgreSQL DROP TABLE force
• PostgreSQL DROP TABLE wildcard
• PostgreSQL DROP TABLE with prefix

PostgreSQL DROP TABLE

You can remove table(s) from the database in PostgreSQL by using the statement DROP TABLE. It destroys the table with the indexes, rules, triggers, and constraints related to that table. The syntax is as follows:

DROP TABLE [IF EXISTS] table_name 
[CASCADE | RESTRICT];

In the above syntax,

• table_name is the name of the table that you want to remove from the database.
• IF EXISTS, CASCADE and RESTRICT are the optional keywords. We will discuss them later in the post.
• NOTE – You have to be the owner of the tables that you are removing or a superuser to remove those tables from the database, else you can’t.

Example:

-- I have some already existing tables and objects

\d

DROP TABLE player_data;

\d

Read: How to find primary column name in Postgresql

PostgreSQL DROP Multiple Tables

You can remove multiple tables from a database in one statement in PostgreSQL by specifying the comma-separated name of the tables after the statement DROP TABLE. The syntax is as follow:

DROP TABLE [IF EXISTS]
table_name1,
table_name2,
...
...
...
table_nameN
[CASCADE | RESTRICT];

In the above syntax, the table_name1, table_name2, …, table_nameN is the name of the tables that has to be removed from the database.

Example:

\d

DROP TABLE
player_import,
student_data,
employee_data;

\d

You can see in the above example some other objects that are SEQUENCE automatically removed, as the tables we have removed are the owner of these objects.

Read PostgreSQL INSERT Multiple Rows

PostgreSQL DROP TABLE CASCADE

Let’s discuss now the keyword CASCADE. When the table that you want to remove is referenced by a FOREIGN KEY constraint of some other table or a VIEW, CASCADE must be specified, as CASCADE removes any dependent VIEW entirely and removes the FOREIGN KEY constraint from the referencing table (another table). The syntax is as follows:

DROP TABLE table_name
CASCADE;

In the above syntax, you can also specify more than one table to be removed from the database, and the CASCADE keyword will do the same for all the tables.

If you don’t want to force, dropping off the table to which other objects depend, you can skip the CASCADE keyword or you can specify the keyword RESTRICT which is by default for the DROP TABLE statement, that refuse to drop the table if any objects depend on it. The syntax is as follow:

DROP TABLE table_name
RESTRICT;

Example:

-- Let's create some tables to perform the tasks

CREATE TABLE department (
dep_id SERIAL PRIMARY KEY,
department_name VARCHAR(80));

CREATE TABLE employee_data (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(20),
dep_id INT NOT NULL,
designation VARCHAR(50),
CONSTRAINT department_fkey
FOREIGN KEY(dep_id) REFERENCES department);

\d

-- Now let's do the task

\d employee_data

\d department

DROP TABLE department;

DROP TABLE department
RESTRICT;

DROP TABLE department
CASCADE;

\d

Read PostgreSQL DROP COLUMN

PostgreSQL DROP TABLE CASCADE sequence

If you have created a sequence and used it for a column’s default of a table in PostgreSQL, then you can remove the table with the sequence associated with it, using the DROP TABLE statement with the keyword CASCADE.

But the sequence has to be owned by the table to be dropped, if not then, you have to change the owner of the sequence to, the column of the table. The syntax is as follow:

[ALTER SEQUENCE sequence_object_name
OWNED BY table_name.column_name;]   -- if the owner is not the table then do this

DROP TABLE table_name
CASCADE;

In the above syntax,

• ALTER SEQUENCE is the statement used to modify the sequence sequence_object_name.
• OWNED BY clause is used to change the owner of the sequence to the table table_name‘s column column_name.
• Then, the DROP TABLE statement with CASCADE keyword is used to remove the table table_name forcefully with its associated objects.

Example:

-- When the owner of the sequence is the table that you are dropping

\ds

DROP TABLE employee_data;

\ds

-- When the owner of the sequence is not the table you are dropping

CREATE SEQUENCE new_employee_data_id_seq;

CREATE TABLE employee_data (
id INT PRIMARY KEY DEFAULT nextval('new_employee_data_id_seq'),
name VARCHAR(50),
gender VARCHAR(20),
dep_id INT NOT NULL,
designation VARCHAR(50));

\d

DROP TABLE employee_data;

\ds

-- After you changed the ownership of the sequence to the table's column

CREATE TABLE employee_data (
id INT PRIMARY KEY DEFAULT nextval('new_employee_data_id_seq'),
name VARCHAR(50),
gender VARCHAR(20),
dep_id INT NOT NULL,
designation VARCHAR(50));

ALTER SEQUENCE new_employee_data_id_seq
OWNED BY employee_data.id;

DROP TABLE employee_data;

\ds

\d

Read PostgreSQL INSERT INTO table + 9 Examples

PostgreSQL DROP TABLE IF EXISTS

You can execute the DROP TABLE statement to remove the table only if already exists by specifying the IF EXISTS keyword after the DROP TABLE statement, which will check the table’s existence before executing the DROP TABLE statement. The syntax is as follows:

DROP TABLE IF EXISTS table_name 
[CASCADE | RESTRICT];

In the above syntax,

• If there is no such table exists that you want to remove, then PostgreSQL issues an error. Hence, to prevent the error statement, IF EXISTS keyword is used, that skips the DROP TABLE statement in such case.

Example:

\d

DROP TABLE emplyee_data;

DROP TABLE IF EXISTS employee_data;

DROP TABLE IF EXISTS new_player_data;

\d

PostgreSQL DROP TABLE CASCADE IF EXISTS

You can combine the keywords CASCADE, and IF EXISTS and get the result accordingly in PostgreSQL, by specifying both the keywords in the DROP TABLE statement.

The syntax is as follow:

DROP TABLE IF EXISTS 
table_name1 [, table_name2, ...] 
CASCADE;

In the above syntax, you can also implement the combined effect to multiple tables by specifying the tables’ comma-separated name in the DROP TABLE statement.

Example:

-- I have already created some tables with foreign key constraint and 
-- views to demonstrate the example

\d

DROP TABLE IF EXISTS
department, employee_data, random_table;

DROP TABLE
department, employee_data, random_table
CASCADE;

DROP TABLE IF EXISTS
department, employee_data, random_table
CASCADE;

\d

Read PostgreSQL ALTER TABLE

PostgreSQL DROP TABLE force

You can remove a table forcefully irrespective of its dependencies with other objects in PostgreSQL by using the CASCADE keyword in the DROP TABLE statement as explained in the above topics. SO, the syntax is the same and shown as follow:

DROP TABLE table_name
CASCADE;

We have discussed this syntax in the previous topic, so let’s do some examples for practice.

Example:

Read How to create a view in PostgreSQL

PostgreSQL DROP TABLE wildcard

You can drop table(s) whose name follows some pattern like some prefix, by using percentage sign (%), also known as wildcard, in the PostgreSQL by creating a STORED FUNCTION and using plpgsql language syntax for pattern matching and executing the DROP TABLE statement in it.

The syntax is as follow:

CREATE OR REPLACE FUNCTION function_name(IN _schema TEXT, IN _wildcard TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row record;
BEGIN
    FOR row IN 
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_type = 'BASE TABLE'
        AND table_schema = _schema
        AND table_name ILIKE (_wildcard || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE';
        RAISE INFO 'Dropped tables: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

SELECT function_name('public', 'tablename_prefix');

In the above syntax,

• First, created a function with name function_name with parameters accepting schema in _schema and prefix of the name of the table tablename_prefix in the _wildcard.
• Before the LOOP, selecting all the tables with the prefix _wildcard.
• Now, inside the LOOP, execute the DROP TABLE statement for the returned tables from the SELECT query above and also issue the name of the tables that are dropped.

After creating the stored function, call the function by specifying the arguments to the FUNCTION function_name.

Example:

CREATE OR REPLACE FUNCTION wild_func(IN _schema TEXT, IN _wildcard TEXT)
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
   row record;
BEGIN
   FOR row IN
      SELECT table_schema, table_name
      FROM information_schema.tables
      WHERE table_type = 'BASE TABLE'
      AND table_schema = _schema
      AND table_name ILIKE (_wildcard || '%')
   LOOP
      EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE';
      RAISE INFO 'Dropped tables: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
   END LOOP;
END;
$$;

\d

SELECT wild_func('public', 'store');

\d

Read PostgreSQL DATE Functions with Examples

PostgreSQL DROP TABLE with prefix

You can DROP TABLE by matching the prefix in PostgreSQL by the method explained above, or you can use another method that is, by declaring a DO statement, in which wildcard (%) is used to match the prefix and removes the table(s) that matched by executing the DROP TABLE statement in it. The syntax is as follow:

DO
$do$
DECLARE
   _table text;
BEGIN
   -- escape identifier and schema-qualify
   FOR _table  IN
      SELECT quote_ident(table_schema) || '.' || quote_ident(table_name)      
      FROM   information_schema.tables
      WHERE  table_name LIKE 'tablename_prefix' || '%'
      AND    table_schema NOT LIKE 'pg\_%'   -- excluding system schemas
   LOOP
      EXECUTE 'DROP TABLE ' || _table || ' CASCADE';
      RAISE INFO 'Dropped tables: %', _table;
   END LOOP;
END
$do$;

In the above syntax,

• First, the tables whose names has prefix tablename_prefix are selected excluding the system schema tables, and returned in _table.
• Then inside the LOOP, Executing the DROP TABLE statement by specifying the table names returned above in _table and issue the name of the tables that are dropped.
• NOTE – The method to create a Stored FUNCTION is better as you have to create a FUNCTION only once, and can use it whenever you want.

Example:

\d

DO
$do$
DECLARE
   _table text;
BEGIN
   FOR _table IN
      SELECT quote_ident(table_schema) || '.' || quote_ident(table_name)
      FROM information_schema.tables
      WHERE table_name LIKE 'store' || '%'
      AND table_schema NOT LIKE 'pg\_%'
   LOOP
      EXECUTE 'DROP TABLE ' || _table || ' CASCADE';
      RAISE INFO 'Dropped tables: %', _table;
   END LOOP;
END
$do$;

\d

You may like the following PostgreSQL tutorials:

• PostgreSQL ADD COLUMN
• PostgreSQL vs SQL Server: Detailed Comparison
• PostgreSQL drop all tables

Watch my YouTube video for a better understanding of the topic:

In this PostgreSQL tutorial, we have learned about the PostgreSQL DROP TABLE statement to remove an existing table from the current database and have covered the following topic:

• PostgreSQL DROP TABLE
• PostgreSQL DROP Multiple Tables
• PostgreSQL DROP TABLE CASCADE
• PostgreSQL DROP TABLE CASCADE sequence
• PostgreSQL DROP TABLE IF EXISTS
• PostgreSQL DROP TABLE CASCADE IF EXISTS
• PostgreSQL DROP TABLE force
• PostgreSQL DROP TABLE wildcard
• PostgreSQL DROP TABLE with prefix