The contents of a table / tables in a PostgreSQL database can be deleted in several ways.
Removing table contents using sql:
Deleting the contents of one table:
TRUNCATE table_name; DELETE FROM table_name;
Removing the contents of all named tables:
TRUNCATE table_a, table_b, …, table_z;
Removing the contents of named tables and tables referencing them (I will explain this in more detail later in this answer):
TRUNCATE table_a, table_b CASCADE;
Removing table contents using pgAdmin:
Deleting the contents of one table:
Right click on the table -> Truncate
Removing the contents of a table and tables that reference it:
Right click on the table -> Truncate Cascaded
The difference between delete and truncate:
From the documentation:
DELETE deletes rows that match the WHERE clause from the specified table. If the WHERE clause is missing, 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 for deleting 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 table, it runs faster. In addition, it immediately restores disk space, and does not require a subsequent VACUUM operation. This is most useful for large tables. http://www.postgresql.org/docs/9.1/static/sql-truncate.html
Working with a table referencing another table:
When you have a database with multiple tables, the tables are likely to be relevant. Three tables can be used as an example:
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);
The order table refers to customer tables and loyalty_cards tables. When you try TRUNCATE / DELETE From a table referenced by other tables / s (another table / s has a foreign key constraint for the specified table), you get an error. To remove content from all three tables, you must name all these tables (order is not important)
TRUNCATE customers, loyalty_cards, orders;
or just the table referenced by the CASCADE keyword (you can name more tables than one)
TRUNCATE customers CASCADE;
The same goes for pgAdmin. Right-click on the client table and select Cascade.