PostgreSQL Drop Tables

When we don’t need a table, we can drop this table. PostgreSQL allows us to drop one or more tables using the DROP TABLE statement.

Note: The DROP TABLE statement will permanently delete the table and the rows in the table, please proceed with caution.

Drop Table Considerations

Dropping a table is a dangerous operation. Once one is deleted, it is very difficult for you to recover the data in it.

Be sure to determine the necessity of this action before dropping a table.

If you decide to drop a table, be sure to back up the table and the rows in the table before deleting.

PostgreSQL DROP TABLE syntax

To drop a table from the database, you should be superuser or the owner of the table. Use the DROP TABLE statement as follows:

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

Explanation:

• The table_name is the name of the table to drop.

• You can drop multiple tables in one DROP TABLE statement, separate table names with commas.

• The IF EXISTS option is optional and avoids errors due to table names entered that do not exist.

• The CASCADE | RESTRICT is optional, it indicates the processing strategy if other objects (such as foreign keys, views, triggers, stored procedures, etc.) refer to the table to be dropped. in:

  • CASCADE allows to drop the specified table and objects referencing this table.
  • RESTRICT refuses to drop the table with an error if there are objects referencing the table. It is the default option.

PostgreSQL DROP TABLE Examples

This example demonstrates how to drop a table in PostgreSQL.

We will create two tables users and user_hobbies in the testdb database. Among them, the users table is used to store the user’s name, gender, age and other information and the user_hobbies table is used to store the user’s hobbies.

Create the users table:

CREATE TABLE users (
  user_id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(45) NOT NULL,
  age INTEGER,
  locked BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL
);

Create the user_hobbies table:

CREATE TABLE user_hobbies (
  hobby_id SERIAL NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);

Use the following statement to drop the users table

DROP TABLE users;

ERROR:  cannot drop table users because other objects depend on it
DETAIL:  constraint fk_user on table user_hobbies depends on table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Here, it fails to drop the users table, and PostgreSQL gives an error message. Because the foreign key of the user_hobbies table refers to the users table, if the users table is dropped, the rows in the user_hobbies table will be meaningless.

If you want to force delete this table, use the CASCADE option as follows:

DROP TABLE users CASCADE;

NOTICE:  drop cascades to constraint fk_user on table user_hobbies
DROP TABLE

Here, the users table is dropped, and the foreign key constraints in the user_hobbies table are fk_user also dropped in cascade.

We can view the table definition to verify that if the foreign key was dropped by \d command, as follows:

\d user_hobbies

                                          Table "public.user_hobbies"
  Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
user_id    | integer                     |           | not null |
hobby      | character varying(45)       |           | not null |
created_at | timestamp without time zone |           | not null |
Indexes:
   "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)

Drop a table that does not exist

If you enter a non-existing table name in the DROP TABLE statement, PostgreSQL will give an error as follows:

DROP TABLE x;

ERROR:  table "x" does not exist

You can use the IF EXISTS option to avoid this error, as follows:

DROP TABLE IF EXISTS x;

NOTICE:  table "x" does not exist, skipping
DROP TABLE

Here, the statement passed, and PostgreSQL gave a notification instead of an error.

Conclusion

In PostgreSQL, DROP TABLE statements are used to drop one or more tables.

• The DROP TABLE keyword is followed by the name of the table to drop. If you want to delete multiple tables, separate the table names with commas.
• The IF EXISTS option can avoid errors when dropping a table that does not exist. It is optional.
• The CASCADE option is used to force the deletion of the referenced table.

It is always important to note that the DROP TABLE statement physically deletes the table and the rows in the table. This operation cannot be undone, please operate with caution. Be sure to back up the table you want to drop, or back up the entire database before doing so.