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