Purpose

The purpose of this post is to learn various ways to delete data from a PostgreSQL table. A PostgreSQL installation will be required to follow this tutorial.

1. Clear a Postgres table

One way to delete all data from a table is deleting the table itself. With the following command you will delete data but also the structure of the table itself:

$ psql -c "DROP TABLE [TABLE_NAME]"

Before deleting the table you could always trigger a database/table backup.. just in case :) .

2. Delete data from a PostgreSQL table partially

Sometimes you only need to delete some records of the table. delete can be used together with the where clause to filter which records have to be deleted or clear the entire table without adding a where clause at all.

# Deletes all records from table
$ psql -c "DELETE FROM [TABLE_NAME]"

# Filters which records should be deleted.
# Example deleting records with COLUMN greather than 100:
$ psql -c "DELETE FROM [TABLE_NAME] where [COLUMN] > 100"

# The WHERE clause can be combined with multiple operators
# like AND, OR, and NOT. Example deleting records with COLUMN
# greather than 100 and COLUMN2 is true:
$ psql -c "DELETE FROM [TABLE_NAME] where [COLUMN] > 100 \
AND [COLUMN2] = TRUE"

# Finally, the WHERE clause can reference other tables.
# Example deleting records from TABLE_1 by joining it
# to the table TABLE_2.
$ psql -c "DELETE FROM [TABLE_1] USING [TABLE_2]
  WHERE [COLUMN] = [TABLE_2].bar AND [TABLE_2].foo = 5"

3. Truncate table in Postgres

If you want to delete all rows from a PostgreSQL table, truncate is the way to go. Postgres truncate table command is way faster than Postgres delete and has the following options:

$ psql -c "TRUNCATE TABLE [TABLE_NAME]"

Adding restart identity will restart all autoincrement seqs of the table. Useful when you want to reset seq ids to 1 for example.

$ psql -c "TRUNCATE TABLE [TABLE_NAME] RESTART IDENTITY"

The Opposite to RESTART IDENTITY is CONTINUE IDENTITY and it’s the default option. This option doesn’t reset auto increment counters.

Adding cascade at the end of the command will delete data from all tables that have a foreign key reference to table_name.

$ psql -c "TRUNCATE TABLE [TABLE_NAME] RESTART IDENTITY CASCADE"

By default Postgres uses STRICT and doesn’t TRUNCATE other tables.

Also, multiple tables can be truncated in the same SQL statement:

$ psql -c "TRUNCATE TABLE [TABLE_1], [TABLE_2], ..., [TABLE_N]"

For more example about PostgreSQL feel free to visit our Postgres tutorial.

Finally, you should definitely take a look at these books to fuel your PostgreSQL knowledge: