PostgreSQL 删除所有内容

PostgreSQL delete all content

提问by vo1d

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

您好，我想删除我的 postgresql 表中的所有数据，但不是表本身。我怎么能这样做？

回答by Greg Hewgill

Use the TRUNCATE TABLEcommand.

使用TRUNCATE TABLE命令。

回答by vitfo

The content of the table/tables in PostgreSQL database can be deleted in several ways.

可以通过多种方式删除 PostgreSQL 数据库中的一个/多个表的内容。

Deleting table content using sql:

使用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:

使用 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

DELETE 从指定表中删除满足 WHERE 子句的行。如果没有 WHERE 子句，效果是删除表中的所有行。 http://www.postgresql.org/docs/9.3/static/sql-delete.html

TRUNCATE 是一个 PostgreSQL 扩展，它提供了一种更快的机制来从表中删除所有行。TRUNCATE 快速从一组表中删除所有行。它与对每个表的非限定 DELETE 具有相同的效果，但由于它实际上并不扫描表，因此速度更快。此外，它会立即回收磁盘空间，而不需要后续的 VACUUM 操作。这在大表上最有用。 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)

表orders 引用表customers 和表loyalty_cards 引用表customers。当您尝试从其他表引用的表中进行 TRUNCATE / DELETE（其他表具有对命名表的外键约束）时，您会收到错误消息。要从所有三个表中删除内容，您必须为所有这些表命名（顺序不重要）

TRUNCATE customers, loyalty_cards, orders;

or just the table that is referenced with CASCADE key word (you can name more tables than just one)

或仅使用 CASCADE 关键字引用的表（您可以命名多个表）

TRUNCATE customers CASCADE;

The same applies for pgAdmin. Right click on customers table and choose Truncate Cascaded.

这同样适用于 pgAdmin。右键单击客户表并选择截断级联。

回答by Erwin Brandstetter

For smalltables DELETEis often faster and needs less aggressive locking (for heavy concurrent load):

对于小表DELETE通常更快并且需要更少的主动锁定（对于重的并发负载）：

DELETE FROM tbl;

With no WHEREcondition.

没有WHERE条件。

For medium or biggertables, go with TRUNCATE tbl, like @Greg posted.

对于中等或更大的桌子，请使用TRUNCATE tbl，就像@Greg 发布的一样。