How to Drop a Table in PostgreSQL?

Board Infinity -

INTRODUCTION

The DROP TABLE statement is used to remove a table from the database:

DROP TABLE [IF EXISTS] table_name 

[CASCADE | RESTRICT];

In this syntax,

  • After the DROP TABLE keywords, add the name of the table you want to delete first.
  • Second, remove the table only if it is there by using the IF EXISTS option.

PostgreSQL generates an error if a table that doesn't exist is deleted. You can use the IF EXISTS option to prevent this scenario.

The DROP TABLE command cannot remove a table if it is referenced by other objects like views, triggers, functions, and stored procedures. You can choose between two alternatives here:

  • The table and any objects depending on it can be deleted using the CASCADE option.
  • If any object depends on the table, the RESTRICT option refuses the removal. If you don't specifically express it in the DROP TABLE statement, the RESTRICT option is the default.

You can add a list of tables, separated by commas, following the DROP TABLE keywords to delete several tables at once:

DROP TABLE [IF EXISTS] 

   table_name_1,

   table_name_2,

   ...

[CASCADE | RESTRICT];

Keep in mind that in order to delete tables, you must be the superuser, schema owner, or table owner.

Examples of PostgreSQL DROP TABLE

Let's look at a few instances of the PostgreSQL DROP TABLE statement in action.

1)Drop any tables that aren't there.

The author table in the database is deleted with the following statement:

DROP TABLE author;

Due to the absence of the author table, PostgreSQL generates an error.

You can utilize the IF EXISTS option in this way to get around the problem.

DROP TABLE IF EXISTS author;

NOTICE: table "author" skipped because it is empty STRIKE TABLE

The output clearly shows that PostgreSQL delivered a notice rather than an error.

2) Eliminate a table with dependent elements.

The authors and pages tables are created by the following:

CREATE TABLE authors (

author_id INT PRIMARY KEY,

firstname VARCHAR (50),

lastname VARCHAR (50)

);


CREATE TABLE pages (

page_id serial PRIMARY KEY,

title VARCHAR (255) NOT NULL,

contents TEXT,

author_id INT NOT NULL,

FOREIGN KEY (author_id) 

          REFERENCES authors (author_id)

);

write your code here: Coding Playground

The author table is dropped using the DROP TABLE command in the following sentence:

DROP TABLE IF EXISTS authors;

PostgreSQL generates an error because the constraint on the page table depends on the author table:

ERROR:  cannot drop table authors because other objects depend on it

DETAIL:  constraint pages_author_id_fkey on table pages depends on table authors

HINT:  Use DROP ... CASCADE to drop the dependent objects too.

SQL state: 2BP01

In this situation, either drop the author table first and then all dependent objects, or utilize the CASCADE option as described below:

DROP TABLE authors CASCADE;

Both the constraint in the page table and the author table are deleted by PostgreSQL.

The following message will be displayed if the DROP TABLE statement deletes the table's dependent objects:

NOTICE:  drop cascades to constraint pages_author_id_fkey on table pages

Conclusion

  • To delete a table, use the DROP TABLE statement.
  • To remove a table and all of its dependent items, use the CASCADE option.