COPY_PARTITIONS_TO_TABLE

Copies partitions from one table to another. This lightweight partition copy increases performance by initially sharing the same storage between two tables. After the copy operation is complete, the tables are independent of each other. Users can perform operations on one table without impacting the other. These operations can increase the overall storage required for both tables.

Note: Although they share storage space, Vertica considers the partitions as discrete objects for license capacity purposes. For example, copying a one TB partition would only consume one TB of space. Your Vertica license, however, considers them as separate objects consuming two TB of space.

Syntax

COPY_PARTITIONS_TO_TABLE (
    '[schema.]source-table',
    'min-range-value',
    'max-range-value', 
    '[schema.]target-table'
)

Parameters

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
source‑table The source table of the partitions to copy.
min‑range‑value The minimum value of the partition to copy. To copy one partition, the minimum and maximum values must be the same.
max‑range‑value The maximum value of the partition to be copied. To copy one partition, the minimum and maximum values must be the same.
target‑table

The target table of the partitions to copy. If the table does not exist, Vertica creates a table from the source table's definition, by calling CREATE TABLE with LIKE and INCLUDING PROJECTIONS clause. The new table inherits ownership from the source table. For details, see Replicating a Table..

Privileges

Table Attribute Requirements

The following attributes of both tables must be identical:

Table Restrictions

The following restrictions apply to the source and target tables:

Examples

If you call COPY_PARTITIONS_TO_TABLE and the target table does not exist, the function creates the table automatically. In the following example, the target table partn_backup.tradfes_200801 does not exist. COPY_PARTITIONS_TO_TABLE creates the table and replicates the partition. Vertica also copies all the constraints associated with the source table except foreign key constraints.

=> SELECT COPY_PARTITIONS_TO_TABLE (
          'prod_trades',
          '200801',
          '200801',
          'partn_backup.trades_200801');
COPY_PARTITIONS_TO_TABLE                          
-------------------------------------------------
 1 distinct partition values copied at epoch 15. 
(1 row)