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?

Answer 1

Use the TRUNCATE TABLE command.

Answer 2

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 -> Truncate

Deleting content of table and tables that reference to it:

Right click on the table -> 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.

Answer 3

For small tables DELETE is often faster and needs less aggressive locking (important for concurrent load):

DELETE FROM tbl;

With no WHERE condition.

For medium or bigger tables, go with TRUNCATE, like @Greg posted:

TRUNCATE tbl;

Hard to pin down the line between "small" and "big", as that depends on many variables. You'll have to test in your installation.

Answer 4

I found a very easy and fast way for everyone who might use a tool like DBeaver: You just need to select all the tables that you want to truncate (SHIFT + click or CTRL + click) then right click

And if you have foreign keys, select also CASCADE option on Settings panel. Start and that's all it takes!

Answer 5

Truncate all tables present in a PostgreSQL database

SET session_replication_role = 'replica';


SELECT tablename FROM pg_tables WHERE schemaname = 'schemaname';

-- Generate and execute the truncate command for each table
DO $$ DECLARE
    table_name TEXT;
BEGIN
    FOR table_name IN (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'schemaname') LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(table_name) || ' CASCADE;';
    END LOOP;
END $$;


SET session_replication_role = 'origin';


COMMIT;