PostgreSQL Delete All Rows Tutorial

Introduction to the PostgreSQL delete all rows tutorial

When you’re managing data in PostgreSQL, there are times when it’s better to delete the contents of a table rather than using the DROP TABLE command. If you want to retain the column schema for the table for future use, it’s preferable to delete all the rows in the table and truncate the table. In this article, we’ll explain how to connect to PostgreSQL and delete all rows in a specific table.

Prerequisites to using PostgreSQL and Node.JS

Before we attempt to use PostgreSQL to delete all rows in a table, it’s important to review the prerequisites for this task:

• You’ll need to have PostgreSQL installed on your machine. You can check if the status is active using the command service postgresql status.

• You’ll also need to have psql installed on your machine. To confirm that this command-line interface for Postgres is installed and running, use the command psql -V.

Accessing the PostgreSQL using the ‘psql’ command-line interface

We’ll be using following command to access a PostgreSQL database on the localhost server using psql:

You will be prompted to enter a password. After you enter it, type the following command to access your specific database:

Again, you’ll be prompted for the user’s password. Enter it and press RETURN.

Backing up the PostgreSQL table records

Keep in mind that the psql commands we’ll be executing in this article cannot be undone. It’s important to check your records carefully before you delete them. You may even want to back up your table and its records before proceeding with this tutorial.

You can use the following SQL statement to copy the table you’d like to delete:

The response will include the SELECT SQL keyword with an integer value representing the number of records copied over from the old table that we’ll be using in our DELETE and TRUNCATE TABLE examples.

Postgres ‘TRUNCATE TABLE’ keywords

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

In the following example, we see how to truncate a table to remove its records while retaining the column schema:

When you execute the above SQL statement, you’ll receive a response of TRUNCATE TABLE. If you then try to execute a SELECT statement to return all of the rows, it will only return the column schema for the table:

The ‘DELETE’ statement in PostgreSQL

Although TRUNCATE is generally faster and easier, the DELETE statement in PostgreSQL is preferable for removing only records that match certain conditions. You can use the SQL WHERE clause to specify the exact criteria that will be used to identify records that will be deleted.

The following syntax is used to perform a DELETE with a WHERE clause to remove targeted records from a table:

In the example shown below, we delete specific records from an example table called “students” where the record’s ID is less than or equal to the integer value 3:

If no records meet our conditions, then psql will return the following:

Delete all of the Postgres table records by a matching condition

Since our hypothetical “students” table contains only student IDs with a value greater than zero, we could execute the following SQL statement to delete all of the table’s records:

This statement should return a result that looks like this:

If you try to use the SELECT statement after deleting all the records, the output will look like this:

WARNING: Remember that the DELETE and TRUNCATE SQL statements shown in this tutorial are irreversible. Only test these SQL statements on table data that you’re absolutely sure you won’t miss.

Conclusion on the PostgreSQL delete all rows tutorial

When you’re done using a table in PostgreSQL, you may not always want to DROP the entire table. If you’d prefer to delete the contents of a table while retaining its column schema, you can use the DELETE FROM and TRUNCATE TABLE commands to accomplish the task. In this article, we looked at a few examples where we connected to PostgreSQL and deleted all rows in a table. Using these examples, you’ll be able to remove records from your own PostgreSQL tables without destroying the table schema.