How to Delete All Table Rows in PostgreSQL

1. Delete All Rows Using the TRUNCATE Command in PostgreSQL
2. Delete All Rows Using the DELETE Command in PostgreSQL
3. Delete All Rows in PostgreSQL Using pgAdmin
4. Delete All Rows of a Referenced Table in PostgreSQL

The problem at hand is deleting all the rows, or tuples, from a table but not deleting the table itself. This means that all the data from the table will be erased and emptied.

In PostgreSQL, there are three ways in which this can be done. Let us discuss all of them one by one.

Delete All Rows Using the TRUNCATE Command in PostgreSQL

The TRUNCATE command empties all the data in the table without scanning it, making it faster and more suitable for larger tables. Let us look at the syntax of deleting all rows using the TRUNCATE command.

TRUNCATE table_name;

To illustrate its usage, let us take a sample database and table as follows:

create table my_table (
id int not null,
name varchar(30) not null,
constraint pk_student primary key (id)
);

insert into my_table values (1,’ First’), (2, ‘Second’), (3, ‘Third’);

The state of my_table can be shown using this statement.

Select * from my_table;

This returns the following table:

Now, let us delete all the inserted rows by running the TRUNCATE command as follows:

TRUNCATE my_table;

After running this command, if we select all rows of the table, it will display an empty table:

This shows that all rows of the table have been deleted.

We can also use the TRUNCATE command to empty or erase multiple tables simultaneously. The syntax for this is shown below.

TRUNCATE table1, table2, …, table3;

Now, let us see the second way to delete all rows from a table.

Delete All Rows Using the DELETE Command in PostgreSQL

The DELETE command is mainly used to delete specific rows from a table that follow some condition. For example, in my_table above, we can delete a particular row by using the DELETE command like this:

DELETE FROM my_table WHERE id = 1;

We can specify no condition to delete all rows using the DELETE command. The effect of running this command will be an empty table as all rows will be deleted.

The syntax is as follows:

DELETE FROM table_name;

Let us use this command on our sample table my_table.

DELETE FROM my_table;

After running this command, if we list the table using the select * from my_table; statement, it will generate an empty table like this:

The DELETE command is slower than TRUNCATE because DELETE scans every table row for the condition and deletes them individually. Therefore, DELETE is not recommended for larger table sizes.

Delete All Rows in PostgreSQL Using pgAdmin

Yet another way to delete all rows from a table is by directly using the pgAdmin tools to manipulate the database. You can use this by following the steps explained below.

In pgAdmin, open your database in the Browser on the left side, as shown in this image:

Click on the Schemas option.

From the drop-down list, right-click on the table whose rows you want to delete. Some options will be displayed as shown here:

Click on the Truncate option. From among the available options, click on Truncate again.

Now, if you list all table rows, it will display an empty table, verifying that the truncation has worked.

Delete All Rows of a Referenced Table in PostgreSQL

If we want to delete all rows from a table, we must remember if any row is referenced in another table. The effect must be cascaded during truncation if some attribute is a foreign key in another table.

Otherwise, we will get a foreign key constraint violation error. Let us explain this using an example of the following two tables:

create table student (
id int not null,
name varchar(30) not null,
constraint pk_student primary key (id)
);

create table grades (
student_id int not null,
grade varchar(1) not null,
course varchar(30) not null,
constraint pk_grades primary key (student_id, course),
constraint fk_grades foreign key (student_id) references student(id)
);

We can see that the table grades references one attribute from the table student. Now, we fill these tables with some data:

insert into student values (1, 'First'), (2, 'Second');
insert into grades values (1, ‘A’, 'Database Systems'), (1, ‘B’, 'Programming Fundamentals'), (2, ‘B’, 'Database Systems');

To delete all rows from the student table, we must also delete them from the grades table because it references the student table. To do this, we can either use the TRUNCATE command on both tables like this:

TRUNCATE student, grades;

Or, we can use the CASCADE keyword after TRUNCATE in the following way:

TRUNCATE student CASCADE;

The CASCADE keyword will also delete all rows from the table with foreign key references to the student table, in this case, the grades table.

Another way to do cascaded truncation is through the pgAdmin tools. This can be done through the following simple steps.

First, go to the Browser on the left and follow the steps described earlier to get to your table. Now, right-click on the table; the following options will be shown.

From there, select the Truncate option. It will list down the following:

Click on Truncate Cascade. This will delete all rows of the selected table and those tables with a foreign key reference to it.

This sums up all the different ways to delete all rows from a table in PostgreSQL. We hope you have learned the use of the DELETE and TRUNCATE commands for deleting all rows and some pgAdmin tools that allow us to do so.