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. Dropping an entire 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. Deleting data from a PostgreSQL table partially

Sometimes you only need to delete some records of the table. Using delete you can use the where clause to filter which records will be deleted or delete all data without specifying a where clause.

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


3. Truncating a Postgres table

If you want to delete all rows from a Postgres table, truncate is the way to go. Performs much faster than delete and contains 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.

Finally, 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.