PostgreSQL Drop Table in an ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The PostgreSQL DROP TABLE statement deletes, or drops, a table definition from the database along with all of the table’s related data, including any constraints and indexes. However, care must be taken when executing the drop table statement, as once the table has been dropped all data contained in the table is permanently deleted and cannot be recovered. Typically, only users with owner-level access can execute the PostgreSQL drop table statement. This tutorial will cover how to use a PostgreSQL drop table for an ObjectRocket instance.

Prerequisites

  • A PostgreSQL for ObjectRocket Instance must be setup before using the PostgreSQL drop table for an ObjectRocket instance. The following section provides details on how to set up the instance for using the DROP TABLE statement on ObjectRocket.

Setup an Instance on ObjectRocket

Log into the user’s ObjectRocket account and then click the create instance tab to setup an instance. If an account has not already been established, instructions are provided on the website.

Connect the PostgreSQL to ObjectRocket Instance

For Windows systems, the following example demonstrates how to connect the ObjectRocket instance of PostgreSQL in Windows using the SQL shell:

1
2
3
4
5
Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud
DATABASE [PostgreSQL]:
Port [5432]: 4144
Username [PostgreSQL]: orkb
Password FOR USER orkb:

For UNIX-based operating systems, execute the psql command in a terminal window to connect to the ObjectRocket instance.

Following is an example of how to connect to an ObjectRocket instance of PostgreSQL in a UNIX terminal:

1
2
3
4
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \
    -U orkb \
    -d PostgreSQL \
    -p 4144

PostgreSQL Drop Table

As the PostgreSQL DROP TABLE statement is used to permanently delete a table from the database, it also removes all of the records on the table.

Execute the following syntax to perform the DROP TABLE function:

1
DROP TABLE [ IF EXISTS ] TABLE_NAME [, ...] [ CASCADE | RESTRICT ]

Following is a breakdown of the above syntax:

  • The table_name is the name of the table targeted for deletion.
  • The IF EXIST keywords are used to prevent errors if the table should not exists the database.
  • The CASCADE command is used to remove all objects from the table that may prevent the table from being deleted.
  • The RESTRICT command is used to prevent deleting a table that has another object that depends on the table.

Drop Table Example

This section will cover creating an example table to use for executing the drop a table statement.

Create Table Example

Executing the following commands will create a three tables named: 'objrkt'; 'fruit_farm'; and 'fruits':

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE objrkt (
obj INT
);
CREATE TABLE fruit_farm (
id INT PRIMARY KEY,
fuit VARCHAR
);
CREATE TABLE fruits (
id INT PRIMARY KEY,
fruit VARCHAR,
fruit_id INT REFERENCES fruit_farm (id)
);

Now list all the table in the database using the following \dt command:

1
2
3
4
5
6
7
8
\dt
          List OF relations
 Schema |    Name    | TYPE  | Owner
--------+------------+-------+-------
 public | fruit_farm | TABLE | orkb
 public | fruits     | TABLE | orkb
 public | objrkt     | TABLE | orkb
(3 ROWS)

Drop a Table

With the example tables now properly prepared, the first example will drop the table named 'objrkt' using the following statement:

1
DROP TABLE objrkt;

Now note, when all of the table in the database are listed again, the'objrkt' table has been deleted.

1
2
3
4
5
6
7
\dt
          List OF relations
 Schema |    Name    | TYPE  | Owner
--------+------------+-------+-------
 public | fruit_farm | TABLE | orkb
 public | fruits     | TABLE | orkb
(2 ROWS)

If the same table is selected again for removal, the system will throw an error, as shown here:

1
2
DROP TABLE objrkt;
ERROR:  TABLE "objrkt" does NOT exist

As shown in the following example, using the IF EXIST keyword will avoid receiving this type of error:

1
2
3
DROP TABLE IF EXISTS objrkt;
NOTICE:  TABLE "objrkt" does NOT exist, skipping
DROP TABLE

NOTE: Using the IF EXIST keyword will only cause the system to create a notice that the table does not exist.

Drop Table With Restrict

If dropping a table that may have external objects that are dependent on the table, execute the following RESTRICT statement to prevent the table from being deleted:

1
2
3
4
DROP TABLE fruit_farm RESTRICT;
ERROR:  cannot DROP TABLE fruit_farm because other objects depend ON it
DETAIL:  CONSTRAINT fruits_fruit_id_fkey ON TABLE fruits depends ON TABLE fruit_farm
HINT:  USE DROP ... CASCADE TO DROP the dependent objects too.

Here the table is not deleted, but the system provides a “hint” that the CASCADE statement can be used to delete the table that has objects that are dependent on it.

Drop Table With Cascade

Execute the following CASCADE command to delete the table that has dependent objects:

1
2
3
DROP TABLE fruit_farm CASCADE;
NOTICE:  DROP cascades TO CONSTRAINT fruits_fruit_id_fkey ON TABLE fruits
DROP TABLE

Now list all of the tables again:

1
2
3
4
5
6
\dt
        List OF relations
 Schema |  Name  | TYPE  | Owner
--------+--------+-------+-------
 public | fruits | TABLE | orkb
(1 ROW)

Note that the table named 'fruit_farm', that had dependent objects, has now been successfully deleted.

Conclusion

This tutorial explained how to use a PostgreSQL drop table for an ObjectRocket instance. The tutorial first explained how to setup an instance on ObjectRocket and connect the PostgreSQL to the ObjectRocket instance. The article then covered how to connect to the instance with Windows- and UNIX-based operating systems. The tutorial then explained how to create sample tables and provided examples on how to execute the drop a table statement along with a breakdown of the various drop-table commands, including the IF EXIST, RESTRICT and CASCADE statements. Remember that the drop table function will permanently delete a table and all associated content, so be certain to use this function with care and use the appropriate restrictions when using a PostgreSQL drop table for an ObjectRocket instance.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.