PostgreSQL DROP TABLE

Whether you’re cleaning up your database, restructuring your schema, or preparing for a fresh start, the DROP TABLE command is the best solution.In this PostgreSQL tutorial, we will discuss the PostgreSQL DROP TABLE statement to remove existing table(s) from the current database.

PostgreSQL DROP TABLE

You can remove table(s) from the database in PostgreSQL by using the statement DROP TABLE. It destroys the table, including its indexes, rules, triggers, and constraints, related to that table.

Syntax

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.

PostgreSQL DROP TABLE Example

Example 1: Basic Operation

-- I have some already existing tables and objects

\d

DROP TABLE player_data;

\d

Read: How to find primary column name in Postgresql

Example 2: Dropping Multiple Tables

You can remove multiple tables from a database in one statement in PostgreSQL by specifying the comma-separated names of the tables after the statement DROP TABLE.

The syntax is as follows:

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 have 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 owners of these objects.

Read PostgreSQL INSERT Multiple Rows

Example 3: Dropping the Table using 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.

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

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

Example 4: Dropping the TABLE with the 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.

However, the sequence must be owned by the table to be dropped; otherwise, you must change the owner of the sequence to the column of the table.

The syntax is as follows:

[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 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 the CASCADE keyword is used to remove the table table_name and its associated objects forcibly.

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

Example 5: Dropping the table if Exists

You can execute the DROP TABLE statement to remove the table only if it 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 no such table exists that you want to remove, then PostgreSQL issues an error. Hence, to prevent the error statement, the IF EXISTS keyword is used, which skips the DROP TABLE statement in such a case.

Example:

\d

DROP TABLE emplyee_data;

DROP TABLE IF EXISTS employee_data;

DROP TABLE IF EXISTS new_player_data;

\d

Example 6: DROPPING 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 follows:

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

In the above syntax, you can also implement the combined effect on multiple tables by specifying the tables’ comma-separated names 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

Example 7: DROPPING TABLE forcefully

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 follows:

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

Example 8: DROPPING TABLE with wildcard

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

The syntax is as follows:

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 named 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 names 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

Example 9: DROPPING TABLE with prefix

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

The syntax is as follows:

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 have the 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 issuing the names of the tables that are dropped.
• NOTE – The method to create a stored function is better, as you only have to create a function 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

Best Practices

1. Always Backup Before Dropping Tables

Before executing DROP TABLE commands, especially in production environments, I always create a backup of my database. This ensures I can recover my data if something goes wrong.

pg_dump -U username -d database_name > backup_before_drop.sql

2. Use Transactions for Safety

When dropping multiple tables or performing complex operations, I wrap my commands in a transaction:

BEGIN;
DROP TABLE table1;
DROP TABLE table2;
-- Verify everything looks good
COMMIT;
-- Or if something seems wrong
-- ROLLBACK;

This approach allows me to review the changes before making them permanent.

3. Consider Using TRUNCATE Instead of DROP

If I only need to remove the data from a table but keep its structure, TRUNCATE is a better option than DROP TABLE:

TRUNCATE TABLE customers;

TRUNCATE is faster than DELETE because it doesn’t scan each row, and it keeps the table structure intact.

Video Tutorial

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

Conclusion

Dropping tables in PostgreSQL is a fundamental task that requires careful consideration. By understanding the various options and best practices I’ve mentioned in this article, you can easily achieve this.

Remember these key takeaways:

• Always back up your data before dropping tables
• Use the IF EXISTS clause to make your scripts more robust
• Understand the implications of CASCADE when dropping tables with dependencies
• Consider TRUNCATE instead of DROP when you only need to remove data
• Use transactions for safety when performing multiple operations

You may like the following PostgreSQL tutorials:

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