Deleting PostgreSQL Tables: DROP TABLE and TRUNCATE

a businessman robot is watching a tutorial with the title deleting postgresql tables drop table and truncate in a library
This blog explains how to use the DROP TABLE and TRUNCATE statements to delete PostgreSQL tables, and how to undo them if needed.

1. Introduction

When working with PostgreSQL databases, you may need to delete some tables that are no longer needed. There are two ways to do this: using the DROP TABLE statement or the TRUNCATE statement. Both statements can remove data from a table, but they have different effects and implications.

In this tutorial, you will learn how to use the DROP TABLE and TRUNCATE statements to delete PostgreSQL tables. You will also learn the difference between them, and how to undo them if you make a mistake. By the end of this tutorial, you will be able to:

  • Delete a single table or multiple tables using the DROP TABLE statement
  • Delete all the data from a table without deleting the table itself using the TRUNCATE statement
  • Understand the effects of DROP TABLE and TRUNCATE on constraints, indexes, and sequences
  • Restore a table that was deleted using the DROP TABLE or TRUNCATE statement

Before you begin, make sure you have access to a PostgreSQL database and a tool to execute SQL queries, such as pgAdmin or psql. You can also use an online SQL editor, such as DB Fiddle or SQL Test, to follow along with the examples.

Ready to start deleting PostgreSQL tables? Let’s go!

2. What is the difference between DROP TABLE and TRUNCATE?

The DROP TABLE and TRUNCATE statements are both used to delete data from a PostgreSQL table, but they have different effects and implications. In this section, you will learn the main differences between them and when to use each one.

The DROP TABLE statement deletes a table and all its data, as well as any associated objects, such as constraints, indexes, and sequences. Once you drop a table, you cannot access it or its data anymore, unless you have a backup. The syntax of the DROP TABLE statement is:

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

The optional IF EXISTS clause allows you to avoid an error if the table does not exist. The optional CASCADE clause allows you to delete any dependent objects along with the table, such as views or foreign keys. The optional RESTRICT clause prevents you from deleting the table if there are any dependent objects, which is the default behavior. For example, to delete a table named customers and all its dependent objects, you can use:

DROP TABLE customers CASCADE;

The TRUNCATE statement deletes all the data from a table, but not the table itself or its associated objects. The table structure, constraints, indexes, and sequences remain intact, but the table is empty. The syntax of the TRUNCATE statement is:

TRUNCATE [TABLE] table_name [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT];

The optional TABLE keyword is for clarity only. The optional RESTART IDENTITY clause allows you to reset any identity columns (such as serial or bigserial) to their initial values. The optional CONTINUE IDENTITY clause allows you to preserve the current values of the identity columns, which is the default behavior. The optional CASCADE and RESTRICT clauses have the same meaning as in the DROP TABLE statement. For example, to delete all the data from a table named orders and reset its identity column, you can use:

TRUNCATE TABLE orders RESTART IDENTITY;

So, what are the advantages and disadvantages of using DROP TABLE or TRUNCATE? Here are some key points to consider:

  • DROP TABLE is irreversible, while TRUNCATE is reversible. If you drop a table by mistake, you cannot recover it unless you have a backup. If you truncate a table by mistake, you can restore it using the ROLLBACK command, as long as you are in the same transaction.
  • DROP TABLE is faster than TRUNCATE, but TRUNCATE is faster than DELETE. Dropping a table is a simple operation that does not require scanning the table or deleting the rows one by one. Truncating a table is also faster than deleting all the rows using the DELETE statement, as it does not generate any logs or trigger any rules or triggers.
  • DROP TABLE requires more privileges than TRUNCATE. To drop a table, you need to be the owner of the table or a superuser. To truncate a table, you need to have the TRUNCATE privilege on the table, which is granted by default to the table owner.

As you can see, the DROP TABLE and TRUNCATE statements have different purposes and implications. You should use DROP TABLE when you want to permanently remove a table and all its associated objects. You should use TRUNCATE when you want to quickly delete all the data from a table, but keep the table structure and its associated objects.

2.1. Syntax and examples

In this section, you will see how to use the DROP TABLE and TRUNCATE statements to delete PostgreSQL tables. You will also see some examples of how to use them in different scenarios.

To use the DROP TABLE statement, you need to specify the name of the table that you want to delete. You can also use the optional clauses IF EXISTS, CASCADE, and RESTRICT to control the behavior of the statement. Here is the general syntax of the DROP TABLE statement:

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

For example, suppose you have a table named products that you want to delete. You can use the following statement to drop the table:

DROP TABLE products;

If you are not sure whether the table exists or not, you can use the IF EXISTS clause to avoid an error. For example:

DROP TABLE IF EXISTS products;

If the table does not exist, the statement will do nothing and return a notice instead of an error.

If the table has any dependent objects, such as views, foreign keys, or triggers, you can use the CASCADE clause to delete them along with the table. For example:

DROP TABLE products CASCADE;

This will delete the products table and any objects that reference it. Be careful when using this clause, as it may delete more objects than you intend.

If you want to prevent the deletion of the table if there are any dependent objects, you can use the RESTRICT clause. For example:

DROP TABLE products RESTRICT;

This will only delete the products table if there are no objects that reference it. Otherwise, it will return an error. This is the default behavior of the DROP TABLE statement, so you can omit the RESTRICT clause if you want.

To use the TRUNCATE statement, you need to specify the name of the table that you want to empty. You can also use the optional clauses RESTART IDENTITY, CONTINUE IDENTITY, CASCADE, and RESTRICT to control the behavior of the statement. Here is the general syntax of the TRUNCATE statement:

TRUNCATE [TABLE] table_name [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT];

For example, suppose you have a table named orders that you want to empty. You can use the following statement to truncate the table:

TRUNCATE TABLE orders;

This will delete all the rows from the orders table, but keep the table structure and its associated objects. The TABLE keyword is optional, so you can omit it if you want.

If the table has any identity columns, such as serial or bigserial, you can use the RESTART IDENTITY clause to reset them to their initial values. For example:

TRUNCATE TABLE orders RESTART IDENTITY;

This will delete all the rows from the orders table and reset the value of the identity column to 1.

If you want to preserve the current values of the identity columns, you can use the CONTINUE IDENTITY clause. For example:

TRUNCATE TABLE orders CONTINUE IDENTITY;

This will delete all the rows from the orders table and keep the value of the identity column as it is. This is the default behavior of the TRUNCATE statement, so you can omit the CONTINUE IDENTITY clause if you want.

If the table has any dependent objects, such as views, foreign keys, or triggers, you can use the CASCADE clause to truncate them along with the table. For example:

TRUNCATE TABLE orders CASCADE;

This will delete all the rows from the orders table and any tables that reference it. Be careful when using this clause, as it may truncate more tables than you intend.

If you want to prevent the truncation of the table if there are any dependent objects, you can use the RESTRICT clause. For example:

TRUNCATE TABLE orders RESTRICT;

This will only truncate the orders table if there are no tables that reference it. Otherwise, it will return an error. This is the default behavior of the TRUNCATE statement, so you can omit the RESTRICT clause if you want.

As you can see, the DROP TABLE and TRUNCATE statements have different syntax and options. You should choose the one that suits your needs and use it carefully.

2.2. Effects on constraints, indexes, and sequences

When you use the DROP TABLE or TRUNCATE statement to delete PostgreSQL tables, you should be aware of the effects on the constraints, indexes, and sequences that are associated with the tables. In this section, you will learn how these objects are affected by the deletion of the tables and how to avoid or resolve any issues that may arise.

Constraints are rules that define the relationships between tables or the values that are allowed in the columns. For example, a primary key constraint ensures that each row in a table has a unique identifier, while a foreign key constraint ensures that the values in a column match the values in another table. Constraints help to maintain the integrity and consistency of the data in the database.

Indexes are structures that improve the performance of queries that search, sort, or filter the data in the tables. For example, a B-tree index allows you to quickly find a row by its key value, while a GIN index allows you to efficiently search for multiple values in an array column. Indexes help to speed up the execution of the queries and reduce the load on the database server.

Sequences are objects that generate sequential numbers that can be used as unique identifiers for the rows in the tables. For example, a serial or bigserial column uses a sequence to automatically assign a value to each row that is inserted into the table. Sequences help to simplify the creation and management of the identity columns.

The effects of the DROP TABLE and TRUNCATE statements on the constraints, indexes, and sequences depend on the options that you use and the dependencies that exist between the objects. Here are some key points to consider:

  • DROP TABLE deletes the table and all its associated objects, including the constraints, indexes, and sequences, unless you use the RESTRICT option and there are dependent objects. In that case, the statement will fail and return an error. To delete the table and its associated objects, you need to use the CASCADE option or drop the dependent objects manually before dropping the table.
  • TRUNCATE deletes all the data from the table, but not the table itself or its associated objects. The constraints, indexes, and sequences remain intact, but the table is empty. However, if the table has any foreign key constraints that reference other tables, you need to use the CASCADE option or truncate the referenced tables manually before truncating the table. Otherwise, the statement will fail and return an error.
  • If you use the RESTART IDENTITY option with the TRUNCATE statement, the sequences that are associated with the identity columns of the table will be reset to their initial values. If you use the CONTINUE IDENTITY option or omit it, the sequences will keep their current values.

As you can see, the DROP TABLE and TRUNCATE statements have different effects on the constraints, indexes, and sequences that are associated with the tables. You should be careful when using these statements and check the dependencies and options before deleting the tables.

3. How to delete multiple tables at once

Sometimes, you may need to delete more than one table from your PostgreSQL database at the same time. For example, you may want to delete all the tables that belong to a specific schema or have a common prefix. In this section, you will learn how to use the DROP TABLE and TRUNCATE statements to delete multiple tables at once.

To use the DROP TABLE statement to delete multiple tables, you need to specify the names of the tables that you want to delete, separated by commas. You can also use the optional clauses IF EXISTS, CASCADE, and RESTRICT to control the behavior of the statement. Here is the general syntax of the DROP TABLE statement for multiple tables:

DROP TABLE [IF EXISTS] table_name1, table_name2, ... [CASCADE | RESTRICT];

For example, suppose you have three tables named customers, orders, and products that you want to delete. You can use the following statement to drop the tables:

DROP TABLE customers, orders, products;

This will delete the three tables and all their associated objects, such as constraints, indexes, and sequences. If you want to avoid an error if any of the tables does not exist, you can use the IF EXISTS clause. For example:

DROP TABLE IF EXISTS customers, orders, products;

If you want to delete any dependent objects along with the tables, you can use the CASCADE clause. For example:

DROP TABLE customers, orders, products CASCADE;

This will delete the three tables and any objects that reference them, such as views or foreign keys. If you want to prevent the deletion of the tables if there are any dependent objects, you can use the RESTRICT clause. For example:

DROP TABLE customers, orders, products RESTRICT;

This will only delete the three tables if there are no objects that reference them. Otherwise, it will return an error.

To use the TRUNCATE statement to delete multiple tables, you need to specify the names of the tables that you want to empty, separated by commas. You can also use the optional clauses RESTART IDENTITY, CONTINUE IDENTITY, CASCADE, and RESTRICT to control the behavior of the statement. Here is the general syntax of the TRUNCATE statement for multiple tables:

TRUNCATE [TABLE] table_name1, table_name2, ... [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT];

For example, suppose you have three tables named customers, orders, and products that you want to empty. You can use the following statement to truncate the tables:

TRUNCATE TABLE customers, orders, products;

This will delete all the data from the three tables, but keep the table structures and their associated objects. The TABLE keyword is optional, so you can omit it if you want. If you want to reset the identity columns of the tables to their initial values, you can use the RESTART IDENTITY clause. For example:

TRUNCATE TABLE customers, orders, products RESTART IDENTITY;

If you want to preserve the current values of the identity columns, you can use the CONTINUE IDENTITY clause. For example:

TRUNCATE TABLE customers, orders, products CONTINUE IDENTITY;

If you want to truncate any tables that reference the tables that you specify, you can use the CASCADE clause. For example:

TRUNCATE TABLE customers, orders, products CASCADE;

This will delete all the data from the three tables and any tables that reference them. If you want to prevent the truncation of the tables if there are any tables that reference them, you can use the RESTRICT clause. For example:

TRUNCATE TABLE customers, orders, products RESTRICT;

This will only truncate the three tables if there are no tables that reference them. Otherwise, it will return an error.

As you can see, the DROP TABLE and TRUNCATE statements allow you to delete multiple tables at once. You should use them carefully and check the names and dependencies of the tables before deleting them.

4. How to undo a DROP TABLE or TRUNCATE operation

One of the most common mistakes that you can make when working with PostgreSQL tables is to delete them by accident. Whether you use the DROP TABLE or TRUNCATE statement, you may end up losing important data or breaking your database schema. In this section, you will learn how to undo a DROP TABLE or TRUNCATE operation and restore your tables and data.

The first thing that you should do when you realize that you have deleted a table by mistake is to stop any further changes to the database. This will prevent any new transactions from overwriting the data that you want to recover. You should also check if you have a backup of the database that you can restore. If you do, you can use the pg_dump and pg_restore commands to restore the backup to a new database or to the original one.

If you don’t have a backup, or if the backup is outdated, you may still be able to undo the deletion of the table, depending on the statement that you used and the options that you specified. Here are some scenarios and solutions:

  • If you used the DROP TABLE statement without the CASCADE option, and there are no dependent objects, you can recreate the table using the CREATE TABLE statement and the same definition as before. You can find the definition of the table in the pg_class system catalog table, which stores information about all the tables in the database. For example, to find the definition of a table named customers, you can use the following query:

    SELECT pg_catalog.pg_get_userbyid(relowner) AS owner,
       relname,
       relacl,
       pg_catalog.array_to_string(reloptions || array(select 'toast.' || x from pg_catalog.unnest(toast_reloptions) x), ', ')
    FROM pg_catalog.pg_class
    WHERE relname = 'customers';

    This will return the owner, name, access privileges, and options of the table. You can use this information to recreate the table with the same structure and permissions. However, this will not restore the data that was in the table, only the table itself.

  • If you used the DROP TABLE statement with the CASCADE option, or if there are dependent objects, you can try to recover the table and its associated objects using the pg_upgrade tool. This tool allows you to upgrade your PostgreSQL database to a newer version, but it can also be used to restore a dropped table from the old version. To do this, you need to create a copy of the old database and run the pg_upgrade tool with the –link option, which will create links between the old and new databases instead of copying the data. This will preserve the data that was in the dropped table and its associated objects, and allow you to access them from the new database. For example, to restore a table named customers and its dependent objects, you can use the following steps:

    1. Create a copy of the old database using the pg_basebackup command. For example:
      2. pg_basebackup -D /path/to/backup -Fp -Xs -P
    2. Create a new database using the initdb command. For example:
      3. initdb -D /path/to/newdb
    3. Run the pg_upgrade tool with the –link option. For example:
      4. pg_upgrade --old-bindir /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin --old-datadir /path/to/backup --new-datadir /path/to/newdb --link
    4. Start the new database server and connect to it using the psql command. For example:
      5. pg_ctl -D /path/to/newdb start
psql -d postgres
    5. Find the OID of the dropped table and its associated objects in the pg_class table. For example:
      6. SELECT oid, relname FROM pg_class WHERE relname = 'customers';
    6. Use the ALTER TABLE statement to rename the dropped table and its associated objects to their original names. For example:
      7. ALTER TABLE customers_1234 RENAME TO customers;

    This will restore the table and its associated objects with the same data as before. However, this method is not guaranteed to work in all cases, and it may cause some issues with the database schema or the data consistency. You should always test this method on a copy of the database before applying it to the original one.

  • If you used the TRUNCATE statement without the CASCADE option, and there are no dependent tables, you can try to recover the data from the table using the TOAST mechanism. This mechanism stores large values, such as text or bytea, in a separate table, called the TOAST table, to save space and improve performance. When you truncate a table, the data in the TOAST table is not deleted immediately, but marked as free space. You can use the SELECT statement to access the data in the TOAST table and insert it back into the original table. For example, to recover the data from a table named customers, you can use the following steps:

    1. Find the OID of the table and its TOAST table in the pg_class table. For example:
      2. SELECT oid, relname, reltoastrelid FROM pg_class WHERE relname = 'customers';
    2. Use the SELECT statement to access the data in the TOAST table using the OID. For example:
      3. SELECT * FROM pg_toast.pg_toast_1234;
    3. Use the INSERT statement to insert the data back into the original table. For example:
      4. INSERT INTO customers SELECT chunk_id, chunk_seq, chunk_data FROM pg_toast.pg_toast_1234;

    This will restore the data that was in the table before the truncation. However, this method is not reliable, as the data in the TOAST table may be overwritten by new transactions or vacuumed by the database. You should always try this method as soon as possible after the truncation, and check the data for any errors or inconsistencies.

  • If you used the TRUNCATE statement with the CASCADE option, or if there are dependent tables, you can try to recover the data from the tables using the WAL mechanism. This mechanism records all the changes that are made to the database in a series of files, called the WAL files, which are used for replication and recovery. When you truncate a table, the WAL files contain the information about the data that was in the table before the truncation. You can use the pg_rewind tool to rewind the database to a previous state using the WAL files. For example, to recover the data from a table named customers and its dependent tables, you can use the following steps:

    1. Create a backup of the database using the pg_basebackup command. For example:
      2. pg_basebackup -D /path/to/backup -Fp -Xs -P
    2. Create a recovery.conf file in the backup directory with the following content:
      3. restore_command = 'cp /path/to/wal/%f %p'
recovery_target_time = '2021-01-01 12:00:00'
    3. Start the backup database server and connect to it using the psql command. For example:

      4. 5. Conclusion

      In this tutorial, you have learned how to use the DROP TABLE and TRUNCATE statements to delete PostgreSQL tables. You have also learned the difference between them, and how to undo them if you make a mistake. Here are some key points to remember:

      • DROP TABLE deletes a table and all its associated objects, such as constraints, indexes, and sequences. It is irreversible, unless you have a backup or use the pg_upgrade tool.
      • TRUNCATE deletes all the data from a table, but not the table itself or its associated objects. It is reversible, as long as you are in the same transaction or use the TOAST or WAL mechanisms.
      • Both statements have optional clauses, such as IF EXISTS, CASCADE, RESTRICT, RESTART IDENTITY, and CONTINUE IDENTITY, that control the behavior of the statements and the effects on the dependent objects.
      • Both statements can delete multiple tables at once, by specifying the names of the tables separated by commas.
      • Both statements have different effects on the constraints, indexes, and sequences that are associated with the tables. You should check the dependencies and options before deleting the tables.

      Deleting PostgreSQL tables is a common and useful operation, but it can also be risky and complicated. You should always be careful when using the DROP TABLE or TRUNCATE statement, and make sure you have a backup or a recovery plan in case of an error. You should also test your statements on a copy of the database before applying them to the original one.

      We hope you have enjoyed this tutorial and learned something new. If you have any questions or feedback, please leave a comment below. Thank you for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts