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. 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 much faster than
Postgres 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.
For more example about PostgreSQL feel free to visit our Postgres tutorial.