PostgreSQL

In PostgreSQL, the TRUNCATE statement is used to remove all rows from a table quickly and efficiently. Unlike the DELETE statement, which can be slower and involves more overhead due to logging and triggering, TRUNCATE is a fast operation because it does not generate individual row delete logs and does not fire triggers. It is particularly useful when you need to reset a table to its empty state without affecting its structure.

Key Points about TRUNCATE

1. Basic Syntax

   TRUNCATE TABLE table_name;
   

2. Truncating Multiple Tables

   TRUNCATE TABLE table1, table2;

3. Using CASCADE
   • The CASCADE option will also truncate any tables that have foreign key references to the truncated table.

   TRUNCATE TABLE table_name CASCADE;

4. Reusing Table Identity Values
   • By default, TRUNCATE will reset any identity columns in the table.
   • If you want to keep the current sequence values, use the RESTART IDENTITY option.

   TRUNCATE TABLE table_name RESTART IDENTITY;

Example with Banking Tables

Consider a set of tables for a banking system: customers, accounts, and transactions. Here's how you would use TRUNCATE in this context:

1. Truncate a Single Table

   TRUNCATE TABLE customers;

2. Truncate Multiple Tables

   TRUNCATE TABLE accounts, transactions;

3. Truncate with Cascade
   • If transactions references accounts, and you want to remove all related rows in transactions as well:

   TRUNCATE TABLE accounts CASCADE;

4. Truncate and Reset Identity Columns

   TRUNCATE TABLE accounts RESTART IDENTITY;

Example Code

Here's how you might define and truncate tables in a PostgreSQL database for a banking system:

-- Create tables
CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

CREATE TABLE accounts (
  account_id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  balance DECIMAL(10, 2) NOT NULL
);

CREATE TABLE transactions (
  transaction_id SERIAL PRIMARY KEY,
  account_id INT REFERENCES accounts(account_id),
  transaction_date TIMESTAMP NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

-- Truncate tables
TRUNCATE TABLE customers RESTART IDENTITY;
TRUNCATE TABLE accounts RESTART IDENTITY;
TRUNCATE TABLE transactions RESTART IDENTITY;

This approach ensures that the tables are emptied efficiently and any identity columns are reset, making it easy to reuse the tables for new data.

To gain complete access, login with gmail or outlook, no need of signup. click here