EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial Postgres DROP Table
 

Postgres DROP Table

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 22, 2023

Postgres DROP Table

 

 

Introduction to Postgres DROP Table

In this Postgres DROP Table article, we will learn about how we can manage to drop a table and deleting the table in the PostgreSQL database. Only the owner of the table can drop the table using the DROP TABLE command. This command entirely deletes the table structure, its records, associated objects like Views defined on that table, its indexes, constraints, and restrictions. In case if you just want to delete the contents of the table, you can use the TRUNCATE or DELETE command. Both these commands will keep everything intact related to the target table and remove the records present in the target table. Let us begin with the help of the syntax of the DROP TABLE command.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

DROP TABLE [IF EXISTS] name_of_the_table [CASCADE | RESTRICT];
  • IF EXISTS– It is an optional parameter. The “IF EXISTS” keyword can be used to prevent any errors that may occur when executing the above command in case there is no table with the specified name (“name_of_the_table”) in the current database. When you use the “IF EXISTS” keyword in the query and no table with the specified name “name_of_the_table” is found in the current server, the system will actively display a notification to inform you about it. This operation will not generate any errors.
  • name_of_the_table– It is the name of the table that you wish to drop and is located in the current database.
  • CASCADE– This is used when a foreign key constraint is involved. To handle the target table’s foreign constraints over other tables or its usage in views, you need to specify the CASCADE property. When CASCADE is used, all the related views to the target table are permanently deleted. However, in the case of foreign key constraints, the related table remains unaffected. All its contents remain intact in the table.
  • RESTRICT– The default behavior in the DROP TABLE query is to prevent the dropping of a table if the “CASCADE” or “RESTRICT” options are not specified. In such cases, if other tables reference the table with foreign constraints or if the table is being used in any views, the system will prohibit the dropping of the table.

Example of Postgres DROP Table

sudo su - postgres
psql
\l

Postgres DROP Table 1

We can check all the databases present in our database server using the query \l, which results in the following output currently for my server –

Now, to check what are all the tables present in the Postgres database, we can use \dt command. Here’s how they result in –

\dt

Postgres DROP Table 2

Let us try to delete a certain table in postgres database, say a table named demo. As it does not contain any table named demo, it will throw the error when we fire the query statement –

DROP TABLE demo;

Postgres DROP Table 3

As can be seen, it gives the error saying table “demo” does not exist. Now, is we use IF EXISTS in our above query in the following way, then instead of an error, it will display a notice shown below –

DROP TABLE IF EXISTS demo;

Postgres DROP Table 4

Let us insert some tables in it. The example will contain two tables, namely teams, and developers. Each developer is a member of one team or another, and one team consists of many developers. While maintaining both records in the database, we will first create tables for each one. Firstly let us create a table for teams which will be our main referenced parent table.

CREATE TABLE teams (
id SERIAL PRIMARY KEY,
team_count INTEGER,
department VARCHAR (100)
);

Now, we will create a table for developers which will act as the referencing or child table. There will be one to many relationships between teams and developers table. team_id will be our referencing key, which will refer to the id of the team’s table.

CREATE TABLE developers (
developer_id INTEGER NOT NULL,
team_id INTEGER REFERENCES teams (id),
name VARCHAR (100),
position VARCHAR (100),
technology VARCHAR (100),
PRIMARY KEY (developer_id,team_id)
);

The output of the above two tables is as follows –

Postgres DROP Table 5

Let us verify the table creation by using \dt command that now results in –

\dt

Postgres DROP Table 6

The above query will create just a referencing table ‘developers’ for teams. But while dropping table teams, it will restrict us to drop and not allow if any of the referencing tables are present such as the developers table for the field id of the team’s record, as the default CASCADE/RESTRICT value is RESTRICT. Let us see the query for dropping the table teams –

DROP TABLE teams;

The above query results in the following output, which suggests us use CASCADE.

Postgres DROP Table 7

Now, if we want to delete the table teams with a particular id along with the foreign constraint referencing table developers, then we will use option CASCADE. Here is the syntax using which we can do so while dropping the table teams.

DROP TABLE teams CASCADE;

that gives the following output –

output

In contrast to the previous case, the execution does not result in an error. In contrast, the execution results in the dropping of the “teams” table, along with the foreign constraint associated with the “developer’s” table. After successfully deleting the foreign constraint, the system generates a notice to inform us of the successful removal. Now, if we check all the tables in postgres database using \dt, then it gives the output –

\dt

output 1

Now, we will insert some records in the developers table.

INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(1,2,'Payal','senior SD','Java');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(2,1,'Heena','Developer','Angular');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(3,2,'Sayali','Developer','Hibernate');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(4,3,'Rahul','Support','Digital Marketing');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(5,3,'Siddhesh','Tester','Maven');

and check by selecting records of the developer’s table.

output 2

Let us create a view on the table developers named

CREATE VIEW team2 AS SELECT name,position,technology FROM developers WHERE team_id = 2;

output 3

After selecting from the view team2, you can see that two records are retrieved. Now, if we drop the table developers using the command

DROP TABLE developers ;

output 4

It gives us an error saying a view named team2 exists, and when we use CASCADE in the following manner –

DROP TABLE developers CASCADE;

and select the tables using \dt command, then the output is –

output 5

You can confirm the deletion of the table developers and the view team2 by retrieving values from the view using the following query.

SELECT * from team2;

which gives output –

output 6

Conclusion- Postgres DROP Table

We can use the DROP TABLE query to completely delete a table in PostgreSQL, including its existence, structure, records, restrictions, and indexes. We can utilize the CASCADE option to handle dependencies associated with the target table in PostgreSQL. This includes managing foreign constraints of other tables that reference the target table or its related views. IF EXISTS helps us to throw a notice instead of an error.

Recommended Articles

We hope that this EDUCBA information on the “Postgres DROP Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL EXCEPT
  2. MIN() in PostgreSQL
  3. PostgreSQL Datetime
  4. PostgreSQL Wal

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW