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]"
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.
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.