Use TRUNCATE to clear table data - Hologres

TRUNCATE removes all rows from a table instantly.

Limitations

Hologres supports sequences but only CONTINUE IDENTITY, not RESTART IDENTITY.
TRUNCATE works on standard tables, parent partition tables, and child partition tables.
TRUNCATE does not work on foreign tables.

Syntax

TRUNCATE [ TABLE ] name [, ... ]
    [ CONTINUE IDENTITY ]

CONTINUE IDENTITY preserves the current value of any associated sequence. This is the default behavior.

Note

TRUNCATE does not generate binary logs.
Version behavior:
Before V3.1: TRUNCATE is a Data Definition Language (DDL) statement. Running it on a table with binary logging enabled produces no error and no binary logs.
V3.1 and later: TRUNCATE is a Data Manipulation Language (DML) statement. This reduces pressure on the frontend (FE) when TRUNCATE is called frequently. Running it on a table with binary logging enabled returns an error. To resolve this, use one of the following approaches at the session level:
- Option 1 (recommended): Run the table as DDL by setting SET hg_enable_truncate_as_dml = off.
- Option 2: Disable binary logging by setting SET hg_experimental_generate_binlog = off. This setting affects all tables and DML operations. For details, see Disable binary logging for DML operations on a table.

Examples

Truncate a regular table

CREATE TABLE event (
    id INT,
    name text,
    tag text
);
INSERT INTO event (id, name, tag) VALUES (23, 'buy', 'num');

-- Verify rows exist before truncation
SELECT * FROM event;

TRUNCATE TABLE event;

Truncate a table with a sequence column

CONTINUE IDENTITY is the default. The sequence counter is not reset after truncation.

CREATE TABLE event_1 (
    id serial,
    name text,
    tag text
);
INSERT INTO event_1 (name, tag) VALUES ('buy', 'num');

-- Verify rows exist before truncation
SELECT * FROM event_1;

# CONTINUE IDENTITY is the default.
TRUNCATE TABLE event_1 CONTINUE IDENTITY;