How to use TRUNCATE to clear data in the target table - Hologres

The TRUNCATE statement is used to clear data from a table. This topic describes the TRUNCATE syntax in Hologres.

Limits

The TRUNCATE statement has the following limits:

Hologres supports Sequence, but currently only supports CONTINUE IDENTITY, not RESTART IDENTITY.
Hologres supports executing the TRUNCATE statement on regular tables, partitioned parent tables, and partitioned child tables.
Hologres does not support executing the TRUNCATE statement on foreign tables.

Syntax

The syntax of TRUNCATE is as follows.

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

The CONTINUE IDENTITY parameter does not modify the current value of Sequence.

Note

The TRUNCATE statement does not support the generation of binary logs.
In versions earlier than V3.1, TRUNCATE is a DDL statement. When you execute the TRUNCATE statement on a table with binary logging enabled, no error is reported, and no binary logs are generated.
Starting from V3.1, TRUNCATE is changed to a DML statement, effectively reducing the pressure on the FE node in scenarios with frequent TRUNCATE operations. When you execute the TRUNCATE statement on a table with binary logging enabled, an error is reported. You must disable binary logging at the session level by using the SET hg_experimental_generate_binlog = off; statement.

Examples

Here are examples of using TRUNCATE:

Example 1:

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

SELECT * FROM event;

TRUNCATE TABLE event ;

Example 2:

CREATE TABLE event_1 (
    id serial,
    name text,
    tag text
);

INSERT INTO event_1 (name,tag) values ('buy', 'num');

SELECT * FROM event_1;

#default is CONTINUE IDENTITY
TRUNCATE TABLE event_1 CONTINUE IDENTITY;