TRUNCATE

Description

Statement use to empty a table or set of tables.

Grammar Syntax

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

Click here to go to the PostgreSQL specification for this syntax.

Sample codes scenarios

Basic use without options

Input

-- TABLE without descendants
TRUNCATE TABLE mytable;

-- TABLE with descendants
TRUNCATE TABLE table_with_descendants;

Output

Using ONLY Option

This will generate that the first table

Input

-- TABLE without descendants
TRUNCATE TABLE ONLY mytable;

-- TABLE with descendants
TRUNCATE TABLE ONLY table_with_descendants;

Output

Without table keyword

Input

-- TABLE without descendants
TRUNCATE mytable;

-- TABLE with descendants
TRUNCATE table_with_descendants;

Output

For a list of multiple tables

Input

Truncate TABLE mytable, table_with_descendants;

Output

Using explicit `*` include descendants

Input

-- TABLE without descendants
TRUNCATE TABLE mytable *;
-- TABLE with descendants
TRUNCATE TABLE table_with_descendants *;

Output

Only option should apply to the first specified table when using lists

Input

-- Case 1:
TRUNCATE ONLY mytable, table_with_descendants;

-- Case 2:
TRUNCATE ONLY table_with_descendants, mytable;

Output

Use RESTART OPTION for tables

When using [RESTART IDENTITY] automatically restarts the sequences owned by columns of the truncated tables

Input

TRUNCATE TABLE mytable, table_with_identity RESTART IDENTITY;

Output

Use CONTINUE OPTION for tables

When using [CONTINUE IDENTITY] does not changes the values of the sequence

Input

TRUNCATE TABLE ONLY table_with_identity CONTINUE IDENTITY;

Output

Use CASCADE for tables

When a CASCADE OPTION is specified all tables related by a foreign key will be truncated

Input

-- Case 1:
TRUNCATE mytable CASCADE;

-- Case 2:
TRUNCATE table_with_identity CASCADE;

Output

Use RESTRICTED for tables

When a table has references a EWI is added to be reviewed.

Input

TRUNCATE mytable RESTRICT;

TRUNCATE table_with_identity RESTRICT;

Output