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

-- TABLE without descendants
TRUNCATE mytable;

-- TABLE with descendants
TRUNCATE descendant_table;
TRUNCATE table_with_descendants;

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

-- TABLE without descendants
--** MSC-WARNING - MSCEWI1102 - REMOVED ONLY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE TABLE mytable;

-- TABLE with descendants
--** MSC-WARNING - MSCEWI1102 - REMOVED ONLY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE TABLE table_with_descendants;

Without table keyword

Input

-- TABLE without descendants
TRUNCATE mytable;

-- TABLE with descendants
TRUNCATE table_with_descendants;

Output

-- TABLE without descendants
TRUNCATE mytable;

-- TABLE with descendants
TRUNCATE descendant_table;
TRUNCATE table_with_descendants;

For a list of multiple tables

Input

Truncate TABLE mytable, table_with_descendants;

Output

TRUNCATE TABLE mytable;
TRUNCATE TABLE descendant_table;
TRUNCATE TABLE table_with_descendants;

Using explicit `*` include descendants

Input

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

Output

-- TABLE without descendants
TRUNCATE TABLE mytable;

-- TABLE with descendants
TRUNCATE TABLE descendant_table;
TRUNCATE TABLE table_with_descendants;

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

-- Case 1:
--** MSC-WARNING - MSCEWI1102 - REMOVED ONLY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE mytable;
TRUNCATE descendant_table;
TRUNCATE table_with_descendants;

-- Case 2:
--** MSC-WARNING - MSCEWI1102 - REMOVED ONLY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE table_with_descendants;
TRUNCATE mytable;

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

--** MSC-WARNING - MSCEWI1102 - REMOVED RESTART IDENTITY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE TABLE mytable;
CREATE OR REPLACE SEQUENCE table_with_identity_did
   START 1
   CLONE table_with_identity_did;
ALTER TABLE table_with_identity ALTER did SET DEFAULT table_with_identity_did.NEXTVAL;
TRUNCATE TABLE table_with_identity;

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

--** MSC-WARNING - MSCEWI1102 - REMOVED ONLY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
--** MSC-WARNING - MSCEWI1102 - REMOVED CONTINUE IDENTITY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE TABLE table_with_identity;

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

-- Case 1:
--** MSC-WARNING - MSCEWI1102 - REMOVED CASCADE OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE mytable;
-- Case 2:
--** MSC-WARNING - MSCEWI1102 - REMOVED CASCADE OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE table_with_foreign_key;
TRUNCATE table_with_identity;

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

--** MSC-WARNING - MSCEWI1102 - REMOVED RESTRICT OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE **
TRUNCATE mytable;

--** MSC-WARNING - MSC-PG0028 - THE TRUNCATE RESTRICTED BEHAVIOR SHOULD BE USED WHEN TABLE HAS NOT REFERENCES, IF HAVE REFERENCES PLEASE ADDED. **
TRUNCATE table_with_identity;