A Deep Dive into PostgreSQL’s TRUNCATE Statement

Most relational database management systems support the TRUNCATE statement, and PostgreSQL is no exception.

That said, PostgreSQL has a few differences in the way its TRUNCATE statement works when compared to many other RDBMSs.

In this article, we’ll explore the various features of PostgreSQL’s implementation of the TRUNCATE statement, along with examples to demonstrate.

What is `TRUNCATE`?

TRUNCATE is a statement that can be used to quickly remove all rows from a table or set of tables. Unlike DELETE, TRUNCATE removes data without scanning each row. Also, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. Because of these characteristics, TRUNCATE can be particularly handy on large tables.

Syntax

In PostgreSQL, the syntax for TRUNCATE goes like this:

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

This includes quite a few options that we don’t see in many other RDBMSs, so let’s take a look at them.

Basic `TRUNCATE`

The simplest form of TRUNCATE removes all rows from a single table:

TRUNCATE TABLE planets;

This example removes all rows from the planets table.

Multiple Tables

We can TRUNCATE multiple tables in a single command:

TRUNCATE TABLE planets, explorers;

This removes all rows from the planets and the explorers tables.

The following examples demonstrate the various options we can use when truncating tables in PostgreSQL.

`RESTART IDENTITY` vs `CONTINUE IDENTITY`

The RESTART IDENTITY option automatically restarts sequences owned by columns of the truncated table(s), while CONTINUE IDENTITY preserves the current sequence value (so that it continues where it left off):

TRUNCATE TABLE planets RESTART IDENTITY;
TRUNCATE TABLE planets CONTINUE IDENTITY;

Let’s use a working example to demonstrate how each of these options work.

Suppose we create and populate the following table:

CREATE TABLE planets (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    moons INT,
    discovered_year INT
);

INSERT INTO planets (name, moons, discovered_year) VALUES
('Mercury', 0, -3000),
('Venus', 0, -1610),
('Earth', 1, -1543),
('Mars', 2, 1659),
('Jupiter', 79, 1610),
('Saturn', 146, 1610),
('Uranus', 27, 1781),
('Neptune', 14, 1846);

SELECT * FROM planets;

Result:

 id |  name   | moons | discovered_year 
----+---------+-------+-----------------
  1 | Mercury |     0 |           -3000
  2 | Venus   |     0 |           -1610
  3 | Earth   |     1 |           -1543
  4 | Mars    |     2 |            1659
  5 | Jupiter |    79 |            1610
  6 | Saturn  |   146 |            1610
  7 | Uranus  |    27 |            1781
  8 | Neptune |    14 |            1846

We can see that there are eight rows, and the id column (i.e. the SERIAL column) goes up to 8.

In the following examples, we’ll truncate the table and then repopulate it with data to demonstrate how the RESTART IDENTITY and CONTINUE IDENTITY options work.

We’ll do CONTINUE IDENTITY first:

TRUNCATE TABLE planets CONTINUE IDENTITY;

INSERT INTO planets (name, moons, discovered_year) VALUES
('Mercury', 0, -3000),
('Venus', 0, -1610),
('Earth', 1, -1543);

SELECT * FROM planets;

Result:

 id |  name   | moons | discovered_year 
----+---------+-------+-----------------
  9 | Mercury |     0 |           -3000
 10 | Venus   |     0 |           -1610
 11 | Earth   |     1 |           -1543

We can see that the id column continued from where it left off (numbering started at 9 instead of 1).

Now let’s do RESTART IDENTITY:

TRUNCATE TABLE planets RESTART IDENTITY;

INSERT INTO planets (name, moons, discovered_year) VALUES
('Mercury', 0, -3000),
('Venus', 0, -1610),
('Earth', 1, -1543);

SELECT * FROM planets;

Result:

 id |  name   | moons | discovered_year 
----+---------+-------+-----------------
  1 | Mercury |     0 |           -3000
  2 | Venus   |     0 |           -1610
  3 | Earth   |     1 |           -1543

This time the numbering started back at 1.

`CASCADE` vs `RESTRICT`

The CASCADE option allows TRUNCATE to remove data from dependent tables, while RESTRICT (the default) will prevent the operation if there are any dependent tables:

TRUNCATE TABLE planets CASCADE;
TRUNCATE TABLE planets RESTRICT;

Next, we’ll do an example to demonstrate both options.

First, we’ll create two tables, one with a foreign key constraint that references the other table. We’ll populate both with data:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE employee_projects (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    project_name VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, department)
VALUES
    ('Mickey', 'Clooney', 'Sales'),
    ('Donald', 'Pitt', 'Marketing'),
    ('Goofy', 'Jolie', 'IT'),
    ('Minnie', 'Aniston', 'HR');

INSERT INTO employee_projects (employee_id, project_name)
VALUES
    (1, 'Project A'),
    (2, 'Project B'),
    (3, 'Project C');

SELECT * FROM employees;
SELECT * FROM employee_projects;

Result:

 id | first_name | last_name | department 
----+------------+-----------+------------
  1 | Mickey     | Clooney   | Sales
  2 | Donald     | Pitt      | Marketing
  3 | Goofy      | Jolie     | IT
  4 | Minnie     | Aniston   | HR
(4 rows)

 id | employee_id | project_name 
----+-------------+--------------
  1 |           1 | Project A
  2 |           2 | Project B
  3 |           3 | Project C

Now let’s try to truncate the employees table using RESTRICT:

TRUNCATE TABLE employees RESTRICT;

Result:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "employee_projects" references "employees".
HINT:  Truncate table "employee_projects" at the same time, or use TRUNCATE ... CASCADE.

As mentioned, RESTRICT is the default option, so this is the same error we’d get if we hadn’t specified anything for this option.

In any case, the error message suggests that we either truncate both tables at the same time, or use the CASCADE option.

Let’s use the CASCADE option:

TRUNCATE TABLE employees CASCADE;

SELECT * FROM employees;
SELECT * FROM employee_projects;

Result:

NOTICE:  truncate cascades to table "employee_projects"
TRUNCATE TABLE

The message tells us that the truncate operation has cascaded to the employee_projects table.

Let’s check the contents of both tables:

SELECT * FROM employees;
SELECT * FROM employee_projects;

Result:

 id | first_name | last_name | department 
----+------------+-----------+------------
(0 rows)

 id | employee_id | project_name 
----+-------------+--------------
(0 rows)

Both tables have been truncated, as expected.

Using `ONLY` with Inheritance

The ONLY keyword is used when you want to truncate only the specified table and not its child tables in an inheritance hierarchy.

Suppose we create the following tables and insert data:

-- Create the parent table
CREATE TABLE cosmic_entities (
    entity_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age_in_eons NUMERIC(10, 2),
    energy_level INTEGER
);

-- Create the child table that inherits from cosmic_entities
CREATE TABLE celestial_bodies (
    mass_in_solar_masses NUMERIC(15, 5),
    habitable BOOLEAN
) INHERITS (cosmic_entities);

-- Insert one row into the parent table
INSERT INTO cosmic_entities (name, age_in_eons, energy_level)
VALUES ('Azathoth', 13.8, 9999);

-- Insert one row into the child table
INSERT INTO celestial_bodies (name, age_in_eons, energy_level, mass_in_solar_masses, habitable)
VALUES ('Nebulon-5', 2.7, 750, 1.23456, true);

SELECT * FROM cosmic_entities;
SELECT * FROM celestial_bodies;

Output:

 entity_id |   name    | age_in_eons | energy_level 
-----------+-----------+-------------+--------------
         1 | Azathoth  |       13.80 |         9999
         2 | Nebulon-5 |        2.70 |          750
(2 rows)

 entity_id |   name    | age_in_eons | energy_level | mass_in_solar_masses | habitable 
-----------+-----------+-------------+--------------+----------------------+-----------
         2 | Nebulon-5 |        2.70 |          750 |              1.23456 | t

We can see that the row that was inserted into the child table also appears in the parent table.

Now let’s truncate the parent table only:

TRUNCATE TABLE ONLY cosmic_entities;

SELECT * FROM cosmic_entities;
SELECT * FROM celestial_bodies;

Result:

 entity_id |   name    | age_in_eons | energy_level 
-----------+-----------+-------------+--------------
         2 | Nebulon-5 |        2.70 |          750
(1 row)

 entity_id |   name    | age_in_eons | energy_level | mass_in_solar_masses | habitable 
-----------+-----------+-------------+--------------+----------------------+-----------
         2 | Nebulon-5 |        2.70 |          750 |              1.23456 | t

We can see that only the parent table was truncated. The child table’s data still exists.

Now let’s run it without the ONLY keyword:

TRUNCATE TABLE cosmic_entities;

SELECT * FROM cosmic_entities;
SELECT * FROM celestial_bodies;

Result:

 entity_id | name | age_in_eons | energy_level 
-----------+------+-------------+--------------
(0 rows)

barney=# SELECT * FROM celestial_bodies;
 entity_id | name | age_in_eons | energy_level | mass_in_solar_masses | habitable 
-----------+------+-------------+--------------+----------------------+-----------
(0 rows)

This time the child table’s data was truncated too.

Asterisk (`*`)

We also have the option of including an asterisk (*) after the table’s name to get the same effect as the previous example – to remove data from any child tables.

Let’s try that out. First, we’ll repopulate the tables:

-- Insert one row into the parent table
INSERT INTO cosmic_entities (name, age_in_eons, energy_level)
VALUES ('Azathoth', 13.8, 9999);

-- Insert one row into the child table
INSERT INTO celestial_bodies (name, age_in_eons, energy_level, mass_in_solar_masses, habitable)
VALUES ('Nebulon-5', 2.7, 750, 1.23456, true);

SELECT * FROM cosmic_entities;
SELECT * FROM celestial_bodies;

Result:

 entity_id |   name    | age_in_eons | energy_level 
-----------+-----------+-------------+--------------
         3 | Azathoth  |       13.80 |         9999
         4 | Nebulon-5 |        2.70 |          750
(2 rows)

 entity_id |   name    | age_in_eons | energy_level | mass_in_solar_masses | habitable 
-----------+-----------+-------------+--------------+----------------------+-----------
         4 | Nebulon-5 |        2.70 |          750 |              1.23456 | t
(1 row)

Now let’s try out the * option:

TRUNCATE TABLE cosmic_entities *;

SELECT * FROM cosmic_entities;
SELECT * FROM celestial_bodies;

Result:

 entity_id | name | age_in_eons | energy_level 
-----------+------+-------------+--------------
(0 rows)

barney=# SELECT * FROM celestial_bodies;
 entity_id | name | age_in_eons | energy_level | mass_in_solar_masses | habitable 
-----------+------+-------------+--------------+----------------------+-----------
(0 rows)

Both tables truncated as expected.

Combining Options

We can combine multiple options in a single TRUNCATE command.

Example:

TRUNCATE TABLE planets, explorers, employees
RESTART IDENTITY
CASCADE;

Output:

NOTICE:  truncate cascades to table "employee_projects"
TRUNCATE TABLE

This truncated all tables as specified, along with any descendent tables. It also restarted any sequences and it cleared out any tables with foreign key references to the named tables.

Conclusion

The TRUNCATE statement in PostgreSQL offers a fast and efficient way to remove all data from one or more tables. By understanding its various options, we can tailor its behaviour to suit our specific needs, whether it’s resetting identity columns, handling foreign key constraints, or working with table inheritance.

What is TRUNCATE?

Syntax

Basic TRUNCATE

Multiple Tables

RESTART IDENTITY vs CONTINUE IDENTITY

CASCADE vs RESTRICT

Using ONLY with Inheritance

Asterisk (*)

Combining Options

Conclusion

What is `TRUNCATE`?

Basic `TRUNCATE`

`RESTART IDENTITY` vs `CONTINUE IDENTITY`

`CASCADE` vs `RESTRICT`

Using `ONLY` with Inheritance

Asterisk (`*`)