Usually, we should never have to delete all records from a PostgreSQL database table. When we do we have a few options.

• DELETE
• TRUNCATE

The TRUNCATE command is recommended over the DELETE command if you have to delete all records from the table.

The DELETE command

You might very likely be familiar with the SQL DELETE command. We can use the command to delete a single row, a collection of rows matching a condition, or an entire table. If we were to clear the users table above using the DELETE command, we could use:

DELETE FROM users;

The DELETE command scans the table during the execution of the command. In addition, it doesn't immediately free up space. Following a large scale deletion using the DELETE command, you will have to use CLUSTER command (or for PG versions before 8.3, the VACUUM FULL command would be a better option) to free space and optimize.

The TRUNCATE command

When you have to delete all rows from a table, the TRUNCATE command is more efficient than the DELETE command. TRUNCATE doesn't scan the table and executes immediately. In addition, it also immediately clears space without needing any further optimisation like the DELETE command.

TRUNCATE TABLE users;

The command has few options:

• RESTART IDENTITY
• CONTINUE IDENTITY (default)
• CASCADE
• RESTRICT (default)

The CONTINUE IDENTITY continues the sequences from where they were before the truncate. This is the default behaviour. If you need to restart sequences RESTART IDENTITY. If your table consists of foreign keys, the truncation will fail. If you use the CASCADE it will truncate related tables ⚠️ as well.

References