Use PostgreSQL TRUNCATE TABLE for ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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):

1
2
3
4
5
Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud
DATABASE [postgres]:
Port [5432]: 4144
Username [postgres]: orkb
Password FOR USER orkb:

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:

1
2
3
4
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \
    -U orkb \
    -d postgres \
    -p 4144

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.

1
2
TRUNCATE TABLE TABLE_NAME
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ];
  • 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:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
department_name VARCHAR
);

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR,
dept_id INT REFERENCES departments (dept_id)
);

Insert into table

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

1
2
3
4
5
6
7
8
9
INSERT INTO departments
VALUES ('1', 'Accounting'),
('2', 'Engineering'),
('3', 'Marketing');

INSERT INTO employees
VALUES ('1', 'Bryony Wharton', '1'),
('2', 'Hallam Ramirez', '2'),
('3', 'Aida Brewer', '3');

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':

1
TRUNCATE 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.

1
2
3
4
SELECT * FROM employees;
 emp_id | name | dept_id
--------+------+---------
(0 ROWS)

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.

1
2
3
4
INSERT INTO employees
VALUES ('1', 'Bryony Wharton', '1'),
('2', 'Hallam Ramirez', '2'),
('3', 'Aida Brewer', '3');

The following will truncate the two tables:

1
TRUNCATE TABLE employees, department;

Both of the tables are now empty.

1
2
3
4
5
6
7
8
9
SELECT * FROM employees;
 emp_id | name | dept_id
--------+------+---------
(0 ROWS)

SELECT * FROM departments;
 dept_id | department_name
---------+-----------------
(0 ROWS)

Truncate table cascade

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

1
2
3
4
TRUNCATE TABLE departments RESTART IDENTITY;
ERROR:  cannot TRUNCATE a TABLE referenced IN a FOREIGN KEY CONSTRAINT
DETAIL:  TABLE "employees" REFERENCES "departments".
HINT:  TRUNCATE TABLE "employees" at the same TIME, OR USE TRUNCATE ... CASCADE.

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:

1
2
3
TRUNCATE TABLE departments CASCADE;
NOTICE:  TRUNCATE cascades TO TABLE "employees"
TRUNCATE TABLE

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.