Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 12.8 ALTER TABLE… TRUNCATE PARTITION

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.8 ALTER TABLE… TRUNCATE PARTITION

Use the ALTER TABLETRUNCATE PARTITION command to remove all of the data from the specified partition, leaving the partition structure intact. The syntax is:

      ALTER TABLE table_name TRUNCATE PARTITION partition_name [{DROP|REUSE} STORAGE]

Description

Use the ALTER TABLETRUNCATE PARTITION command to remove all of the data from the specified partition, leaving the partition structure intact. When you truncate a partition, any subpartitions of that partition are also truncated.

ALTER TABLETRUNCATE PARTITION will not cause ON DELETE triggers that might exist for the table to fire, but it will fire ON TRUNCATE triggers. If an ON TRUNCATE trigger is defined for the partition, all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation occurs.

You must have the TRUNCATE privilege on a table to invoke ALTER TABLETRUNCATE PARTITION.

For more information about restrictions that apply to the ALTER TABLETRUNCATE PARTITION, please refer to the TRUNCATE command, in the PostgreSQL Core documentation at:

Parameters

table_name

The name (optionally schema-qualified) of the modified table.

partition_name

The name of the partition to be truncated.

DROP STORAGE and REUSE STORAGE are included for compatibility only; the clauses are parsed and ignored.

Example

To remove all of the data from the staff partition of the employees table, invoke the following command:

      ALTER TABLE employees TRUNCATE PARTITION staff;

Previous PageTable Of ContentsNext Page