Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL DROP TABLE

  • Drop query completely removes a table from database. This command will also destroy the table structure
  • DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)
  • DROP TABLE removes tables from the database. Only its owner can drop a table. To empty a table of rows without destroying the table, use DELETE or TRUNCATE.
  • You have to be careful while using this command because once a table is deleted(dropped) then all the information available in the table would also be lost forever.
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
Parameters:
IF EXISTS
Do not throw an error if the table does not exist. A notice is issued in this case.
name
The name (optionally schema-qualified) of the table to drop.
CASCADE
Automatically drop objects that depend on the table (such as views).
RESTRICT
Refuse to drop the table if any objects depend on it. This is the default.

PRACTICAL FOR DROPPING TABLE:
Step 1. First you need to list down the tables and choose what table do you want to drop and also check that table having any dependencies like index,foregin key,trigger,functions,or any procedure
postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | details       | table | postgres
 public | employe       | table | postgres
 public | place         | table | postgres
 public | school        | table | postgres
 public | school_bk     | table | postgres
 public | school_bk2    | table | postgres
 public | student       | table | postgres
 public | t5            | table | u2
 public | us_address    | table | postgres
 public | us_snail_addy | table | postgres
(10 rows)
Step 2.Next we will drop the "t5" table
postgres=# drop table t5; 
DROP TABLE
Step 3.Here we are are dropping mulltiple table in a single command
postgres=# drop table details,employe;
DROP TABLE
postgres=# drop table place;          
DROP TABLE
postgres=# drop table us_address;
DROP TABLE
Step 4.after dropped the table we need to verfy the table using "\dt" command
postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | school        | table | postgres
 public | school_bk     | table | postgres
 public | school_bk2    | table | postgres
 public | student       | table | postgres
 public | us_snail_addy | table | postgres
(5 rows)
Here 
"IF EXISTS" is not standard; different platforms might support it with different syntax, or not support it at all. In PostgreSQL, the syntax is
postgres=# DROP TABLE IF EXISTS table_name;
The first one will throw an error if the table doesn't exist, or if other database objects depend on it. Most often, the other database objects will be foreign key references, but there may be others, too. (Views, for example.) The second will not throw an error if the table doesn't exist, but it will still throw an error if other database objects depend on it.

To drop a table, and all the other objects that depend on it, use one of these.
DROP TABLE table_name CASCADE;
DROP TABLE IF EXISTS table_name CASCADE;


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL