Use PostgreSQL TRUNCATE TABLE for ObjectRocket Instance

Introduction

In this tutorial we’ll be demonstrating how to use PostgreSQL to truncate a table for an ObjectRocket instance. We will show you how to truncate a single table, truncate multiple tables at once, and how to truncate tables using cascade.

Prerequisites

Before you proceed on the instructions on how to use the ALTER TABLE statement for your ObjectRocket instance, you will need to have the following:

ObjectRocket Instance of PostgreSQL

Set up a PostgreSQL instance on your ObjectRocket account in the create instance tab.

Connection of PostgreSQL to the database

To connect to the instance using a Windows operating system, you can use the command line interface and use the psql command or the SQL Shell (psql).

The following is an example on how to connect to the ObjectRocket instance of PostgreSQL using the SQL Shell (psql):

To connect to the instance using a UNIX-based operating system, like macOS or Linux, you can use the terminal window and use the psql command.

The following is an example on how to connect to the ObjectRocket instance of PostgreSQL using the UNIX terminal:

PostgreSQL TRUNCATE TABLE

The TRUNCATE TABLE is used to delete all the records from the table. It is faster and uses fewer system resources than the DELETE statement. The DELETE deletes the records one by one while the while the TRUNCATE quickly deletes all the records on the table without scanning each record.

• table_name — it is the name of the table to truncate.
• RESTART IDENTITY — the sequences of the truncated table will be automatically restart.
• CONTINUE IDENTITY — the default, the values of the sequences will not be changed.
• CASCADE — it will truncate the table even if it has foreign key reference.
• RESTRICT — it will refuse to truncate table if it has foreign key reference.

Truncate table example

Now, to truncate a table we first need a table so we’ll begin our demo by creating a table and inserting records into it.

Create table example

The following statement will create two tables 'departments' and 'employees' where each employee record has a dept_id that references which department he works in:

Insert into table

The following statement will insert records to the table 'department' and 'employees':

Looking at this data you can see that Bryony Wharton works in Accounting, Hallam Ramirez works in Engineering, and Aida Brewer works in Marketing.

Now that we have a table with data we can proceed with our examples on how to truncate the table using TRUNCATE.

Truncate a table

Using the TRUNCATE we will delete all the records from our table.

The following will delete all the records from the table 'employees':

If we want to validate that all records from employees have been removed you can use a SELECT statement like below, no records will be displayed because all the records are now deleted.

Truncate two tables

You can truncate two or more tables at once, let us try it on our tables. But before we do that, let us insert records back into the 'employees' table since we just truncated it.

The following will truncate the two tables:

Both of the tables are now empty.

Truncate table cascade

Now, what if we try to truncate a table referenced in a foreign key? See the following:

It throws an error because the table we are truncating is referenced by another table. It gives us a hint to truncate the tables at the same time( like what we did in the previous example) or to use CASCADE which will also truncate the employees table. Let us now try to truncate it with CASCADE, see the following:

Both tables are now truncated.

Conclusion

We hope you’ve found this article on how to use PostgreSQL to truncate from the ObjectRocket platform informative. We covered several ways to use the truncate command so that you can use the type that suits your situation. If you don’t have a database to attempt this demo, get a free PostgreSQL database setup in ObjectRocket is easy and it’s just as easy to connect to. Thanks for joining us.