Skip to main content

Drop Table in PostgreSQL


In this tutorial, you will understand how to use the DROP Table Statement to remove existing tables from the database in PostgreSQL.

PostgreSQL DROP table statement is used to define the table and remove all the data, index, rule, trigger and obstacles related to that table.
DROP table removes tables from the database. Only its owner can demolish a table. To clear the table without any losses, use DELETE or TRUNCATE.

The DROP table removes any indexes, rules, triggers, and obstacles that are present for the target table. However, due to viewing the foreign-key block of a table or any other table, CASCADE should be specified. (CASCADE will completely remove a dependent approach, but in case of foreign-key it will only remove foreign-key constraints, not completely other tables.) You should be cautious while using this command because once the table is removed, all the available information in the table will be permanently lost.

Parameters

IF EXISTS: If the table does not exist, do not enter an error. In this case, a notice is issued.

Name: The name of the table to drop (optional schema-worthy).

CASCADE: Automatically reject any items that are dependent on the table (such as views).

RESTRICT: If any object depends on it, then refuse to leave the table. This is the default.

PostgreSQL DROP TABLE syntax

To delete a current table from the database, you use the DROP table statement as shown below:


DROP TABLE [IF EXIST] table_name[CASCADE/RESTRICT];


To permanently delete the table from the database, you specify the name of the table after the DROP TABLE keyword.
If you delete a non-existent table, the PostgreSQL problem is an inaccuracy. To avoid this situation, you can use the IF EXISTS parameter after the DROP table clause.

If the table you want to delete is used in ideas, constraints or any other object, then CASCADE gives you a grant to automatically remove those dependent items with the table.

RESTRICT Refuse to drop the table if it has dependent table (foreign key relation). By default, PostgreSQL uses RESTRICT

After removing several tables of DROP tables together, you can put a list of tables, each table is different from a comma.

Note that only the owner of the schema, superuser and table owner has sufficient authority to remove the table.

PostgreSQL DROP TABLE examples

 Suppose we have a table Table1 and we don’t have it need onwards so we have to remove this table permanently from the database. Use the following syntax


DROP TABLE Table1;


 If Table1 has the dependent table then above query will return the error:

[Err] ERROR:  cannot drop table Table1 because other objects depend on it

DETAIL:  constraint Table2_Table1_id_fkey on table Table2 depends on table Table1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


Use the following syntax if Table1 has a dependent table.


DROP TABLE Table1 CASCADE;


CASCADE removes the relations between tables.

If Table1 doesn’t exist then what will happen, absolutely will raise Error:
[Err] ERROR:  Table Table1 doesn’t exist.

To resolve this issue please use the following syntax


DROP TABLE IF EXISTS Table1 CASCADE;




Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

What is difference between UNION and UNION ALL in SQL Server

We use UNION and UNION ALL operator to combine multiple results set into one result set.
UNION operator is used to combining multiple results set into one result set but removes any duplicate rows. Basically, UNION is used to performing a DISTINCT operation across all columns in the result set. UNION operator has the extra overhead of removing duplicate rows and sorting result.
UNION ALL operator use to combine multiple results set into one result set but it does not remove any duplicate result. Actually, this does not remove duplicate rows so it is faster than the UNION operator. If you want to combine multiple results and without duplicate records then use UNION otherwise UNION ALL is better.
Following some rules for using UNION/UNION ALL operator
1.The number of the column should be the same in the query's when you want to combine them. 2.The column should be of the same data type. 3.ORDER BY clause can be applied to the overall result set not within each result set.
4.Column name of …