PostgreSQL TRUNCATE TABLE: Syntax, Examples & Limitations

By /
Truncate Table In Postgresql

In this tutorial, we will be learning about the TRUNCATE TABLE statement in PostgreSQL which is used to delete records from the table. Many new folks misunderstand the TRUNCATE TABLE with the DROP TABLE statement. We will clear all your misunderstandings in this tutorial. So, be with us and let’s get started!

PostgreSQL TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is available in nearly every database system which is used to delete complete records from the table. Note that, the TRUNCATE TABLE statement doesn’t delete the table itself but only the records in it.

Now you will be thinking, how is the TRUNCATE different from the DELETE statement? Let me clarify it.

The DELETE statement is also used to delete records from the table. However, when you want to delete all the records from the table, the DELETE statement is not as efficient as the TRUNCATE. This is because the DELETE statement generates the log for each record that is being deleted, whereas the TRUNCATE statement generates fewer logs as it works by deallocating the data pages from the table.

Another thing to note is that the TRUNCATE statement doesn’t give you the ability to delete the records conditionally, i.e., you can not use the WHERE clause with the TRUNCATE statement.

Now let’s see the syntax of the TRUNCATE TABLE statement.

PostgreSQL TRUNCATE TABLE Syntax

Following is the syntax of the TRUNCATE statement.

Syntax:

TRUNCATE [ TABLE ]  [ ONLY ] tableName  [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

  • TRUNCATE [ TABLE ] – The main keyword to proceed with. The TABLE keyword is optional. The ONLY keyword is used to truncate all the descendant tables if present any. The * is used to explicitly indicate that descendant tables are included in the truncate statement.
  • tableName – The name of the table that you want to delete all the records from. You can specify multiple table names separated by a comma.
  • RESTART IDENTITY – It is used to restart the sequences of columns of the truncated table.
  • CONTINUE IDENTITY – It is used to continue the sequences of columns of the truncated table.
  • CASCADE – When you specify the CASCADE keyword, PostgreSQL will truncate all tables having foreign-key references.
  • RESTRICT – When you specify the RESTRICT keyword, PostgreSQL will refuse to truncate all other tables having foreign-key references. The RESTRICT is the default.

Now that we have seen the syntax of the TRUNCATE statement, let’s proceed to some demonstrations.

PostgreSQL TRUNCATE TABLE Examples

Before moving forward, we will need a table with some data in it so that we can demonstrate the TRUNCATE statement examples.

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

INSERT INTO authors (author_name) VALUES
    ('J.K. Rowling'),
    ('George Orwell'),
    ('Harper Lee');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created the ‘authors’ table with two columns and inserted data into it. Let’s check if the data has been inserted into the table successfully.

SELECT * FROM authors;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Authors Table Data
Authors Table Data

Perfect!

Delete all data from a single table

Now let’s truncate the data from the ‘authors’ table.

TRUNCATE TABLE authors;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Truncate Authors Table
Truncate Authors Table

As you can see, the ‘authors’ table is truncated successfully.

Truncate data from the table having foreign key reference

Here we have a ‘authors’ table created with no data currently. Let’s say we create another table called ‘books’ which is referenced to the ‘authors’ table via a foreign key. What if you want to truncate the ‘authors’ table as well as the referencing table?

So, let’s create a ‘books’ table first which will be referencing the ‘authors’ table and try to truncate the ‘authors’ table.

-- create books table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    book_title VARCHAR(200) NOT NULL,
    author_id INT REFERENCES authors(author_id)
);

-- Inserting data into authors table
INSERT INTO authors (author_name) VALUES
    ('J.K. Rowling'),
    ('George Orwell'),
    ('Harper Lee');

-- Inserting data into the "books" table
INSERT INTO books (book_title, author_id) VALUES
    ('Harry Potter and the Sorcerer''s Stone', 1),
    ('1984', 2),
    ('To Kill a Mockingbird', 3),
    ('Harry Potter and the Chamber of Secrets', 1);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s print the books table to see if the data has been inserted successfully.

SELECT * FROM books;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Books Table Data
Books Table Data

Perfect!

Now that the ‘authors’ table is referenced by the ‘books’ table data, we will try to truncate the ‘authors’ table.

TRUNCATE TABLE authors;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Foreign Key Constraint Error
Foreign Key Constraint Error

As you can see, PostgreSQL has thrown an error that we can’t truncate the ‘authors’ table because it is referenced by another table. This is because PostgreSQL uses the RESTRICT keyword by default in order to prevent the truncation of linked tables.

To truncate both tables, we have to use the CASCADE keyword. Check below.

TRUNCATE TABLE authors CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Truncate Using CASCADE
Truncate Using CASCADE

As you can see now, the ‘authors’ table as well as the ‘books’ table has been truncated successfully.

Truncate multiple tables

To truncate multiple tables, you have to specify all the table names separated by a comma.

Here we have removed the reference of the ‘authors’ table from the ‘books’ table so that both become independent tables. Let’s try to truncate these two tables in a single query.

TRUNCATE TABLE authors, books;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Truncate Multiple Tables
Truncate Multiple Tables

As you can see, both tables have been truncated successfully without any error or warning.

Limitations of the TRUNCATE Statement

Although the TRUNCATE statement is faster than the DELETE statement to delete all records from the table, it comes with some limitations. Let’s see them one by one.

  1. You can not use the WHERE condition with the TRUNCATE statement because it is used to delete all records instead of a particular set of records.
  2. The ON DELETE triggers don’t work on the TRUNCATE statement. If you want to use the delete triggers on the TRUNCATE statement, use the BEFORE TRUNCATE or the AFTER TRUNCATE triggers.
  3. You can’t use the COMMIT when using the TRUNCATE statement, whereas the DELETE statement can use the COMMIT statement.
  4. You must need the TRUNCATE privilege on the table.

Conclusion

In this tutorial, we have learned so many new things about the TRUNCATE statement that you might have missed in your school or while reading other blogs. I hope this tutorial helped you learn new things. Try different approaches with the TRUNCATE statement in order to learn more things such as using COMMIT, Triggers etc.

Reference

Postgresql official documentation