Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL delete all content

Tags:

postgresql

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

like image 466
vo1d Avatar asked Oct 11 '22 10:10

vo1d


People also ask

How do you delete table data in Pgadmin?

To delete a row, press the Delete toolbar button. A popup will open, asking you to confirm the deletion. To commit the changes to the server, select the Save Data toolbar button.

Which SQL command can be used to delete all records from the table in PostgreSQL?

The easiest way to delete all rows in a table is to use the TRUNCATE TABLE command.

How do I delete an entire column in PostgreSQL?

Syntax. The syntax to drop a column in a table in PostgreSQL (using the ALTER TABLE statement) is: ALTER TABLE table_name DROP COLUMN column_name; table_name.

How do I clear a query in PostgreSQL?

While Ctrl + A moves the cursor to the beginning of the current line, Ctrl + K deletes all characters after the cursor.


2 Answers

Use the TRUNCATE TABLE command.

like image 225
Greg Hewgill Avatar answered Oct 22 '22 05:10

Greg Hewgill


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.

like image 177
vitfo Avatar answered Oct 22 '22 04:10

vitfo