PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
Home / PostgreSQL Tutorial / PostgreSQL DROP TABLE

PostgreSQL DROP TABLE

Summary: in this tutorial, you will learn how to use the PostgreSQL DROP TABLE statement to remove existing tables from the database.

PostgreSQL DROP TABLE syntax

To remove existing table from the database, you use the DROP TABLE statement as shown following:

1
DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

You specify the table name after the DROP TABLE keyword to remove the table permanently from the database.

If you remove a non-existent table, PostgreSQL issues an error. To avoid this situation, you can use the IF EXISTS parameter followed by the DROP TABLE clause.

In case the table that you want to remove is used in views, constraints or any other objects, the CASCADE allows you to remove those dependent objects together with the table automatically.

RESTRICTrefuses to drop table if there is any object depends on it. PostgreSQL uses RESTRICT by default.

You can put a list of tables after the DROP TABLE to remove multiple tables at once, each table separated by a comma.

Notice that only superuser, schema owner, and table owner have sufficient privilege to remove the table.

PostgreSQL DROP TABLE examples

The following statement removes a table named author in the database:

1
DROP TABLE author;

PostgreSQL issues an error because the author table does not exist.

1
[Err] ERROR:  table "author" does not exist

To avoid this error, you can use the IF EXISTS parameter.

1
DROP TABLE IF EXISTS author;

PostgreSQL issues a notice instead of an error.

We create new tables named author and page for the next demonstration:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE author (
author_id INT NOT NULL PRIMARY KEY,
firstname VARCHAR (50),
lastname VARCHAR (50)
);
 
CREATE TABLE page (
page_id serial PRIMARY KEY,
title VARCHAR (255) NOT NULL,
CONTENT TEXT,
author_id INT NOT NULL,
FOREIGN KEY (author_id) REFERENCES author (author_id)
);

You can remove the author table by using the following statement:

1
DROP TABLE IF EXISTS author;

Because the constraint on the page table depends on the authortable, PostgreSQL issues an error message.

1
2
3
[Err] ERROR:  cannot drop table author because other objects depend on it
DETAIL:  constraint page_author_id_fkey on table page depends on table author
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

In this case, you need to remove all dependent objects first before removing the author table or use CASCADE parameter as follows:

1
DROP TABLE author CASCADE;

PostgreSQL removes the author table as well as the constraint in the page table. In addition, it issues a notice:

1
NOTICE:  drop cascades to constraint page_author_id_fkey on table page

In this tutorial, you have learned how to remove existing table from the database by using PostgreSQL ALTER TABLER statement.

Related Tutorials

  • A Step-by-Step Guide To PostgreSQL Temporary Table
Previous Tutorial: PostgreSQL RENAME COLUMN: Renaming One or More Columns of a Table
Next Tutorial: PostgreSQL TRUNCATE TABLE

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete

Managing Table Structure

  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

PostgreSQL Views

  • Managing PostgreSQL Views
  • Creating Updatable Views
  • PostgreSQL Materialized Views
  • The WITH CHECK OPTION Views
  • PostgreSQL Recursive View

PostgreSQL Triggers

  • Introduction to Trigger
  • Creating A Trigger
  • Managing PostgreSQL Triggers

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • How To Change The Password of a PostgreSQL User
  • PostgreSQL AGE Function
  • PostgreSQL DATE_PART Function
  • PostgreSQL List Users
  • PostgreSQL NOW Function
  • PostgreSQL DATE_TRUNC Function
  • PostgreSQL TO_DATE Function: Convert String to Date
  • A Look at PostgreSQL User-defined Data Types
  • PostgreSQL Copy Database Made Easy
  • How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.