PostgreSQL delete all content

Question

Hello I want to delete all data in my postgresql tables  but not the table itself   How could I do this

User · Accepted Answer

Use the TRUNCATE TABLE command

User · Answer

The content of the table tables in PostgreSQL database can be deleted in several ways   Deleting table content using sql   Deleting content of one table   TRUNCATE table name  DELETE FROM table name    Deleting content of all named tables   TRUNCATE table a  table b       table z    Deleting content of named tables and tables that reference to them  I will explain it in more details later in this answer    TRUNCATE table a  table b CASCADE    Deleting table content using pgAdmin   Deleting content of one table   Right click on the table - gt  Truncate   Deleting content of table and tables that reference to it   Right click on the table - gt  Truncate Cascaded   Difference between delete and truncate   From the documentation      DELETE deletes rows that satisfy the WHERE clause from the specified   table  If the WHERE clause is absent  the effect is to delete all rows   in the table    http   www postgresql org docs 9 3 static sql-delete html      TRUNCATE is a PostgreSQL extension that provides a faster mechanism to   remove all rows from a table  TRUNCATE quickly removes all rows from a   set of tables  It has the same effect as an unqualified DELETE on each   table  but since it does not actually scan the tables it is faster    Furthermore  it reclaims disk space immediately  rather than requiring   a subsequent VACUUM operation  This is most useful on large tables    http   www postgresql org docs 9 1 static sql-truncate html   Working with table that is referenced from other table   When you have database that has more than one table the tables have probably relationship   As an example there are three tables   create table customers   customer id int not null  name varchar 20   surname varchar 30   constraint pk customer primary key  customer id      create table orders   order id int not null  number int not null  customer id int not null  constraint pk order primary key  order id   constraint fk customer foreign key  customer id  references customers customer id      create table loyalty cards   card id int not null  card number varchar 10  not null  customer id int not null  constraint pk card primary key  card id   constraint fk customer foreign key  customer id  references customers customer id       And some prepared data for these tables   insert into customers values  1   John    Smith     insert into orders values   10  1000  1    11  1009  1    12  1010  1            insert into loyalty cards values  100   A123456789   1     Table orders references table customers and table loyalty cards references table customers  When you try to TRUNCATE   DELETE FROM the table that is referenced by other table s  the other table s has foreign key constraint to the named table  you get an error  To delete content from all three tables you have to name all these tables  the order is not important   TRUNCATE customers  loyalty cards  orders    or just the table that is referenced with CASCADE key word  you can name more tables than just one   TRUNCATE customers CASCADE    The same applies for pgAdmin  Right click on customers table and choose Truncate Cascaded

User · Answer

For small tables DELETE is often faster and needs less aggressive locking  for heavy concurrent load    DELETE FROM tbl    With no WHERE condition   For medium or bigger tables  go with TRUNCATE tbl  like  Greg posted

[postgresql] PostgreSQL delete all content

Examples related to postgresql