COPY_PARTITIONS_TO_TABLE

Copies partitions from one table to another.

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.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

COPY_PARTITIONS_TO_TABLE (
    '[[{namespace. | database. }]schema.]source-table',
    'min-range-value',
    'max-range-value',
    '[[{namespace. | database. }]schema.]target-table'
     [, 'force-split']
)

Arguments

{ namespace. | database. }
Name of the database or namespace that contains table:

  • Database name: If specified, it must be the current database.

  • Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

For Eon Mode databases, the namespaces of staging-table and target-table must have the same shard count.

schema
Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.
source-table
The source table of the partitions to copy.
min-range-value, max-range-value
The minimum and maximum value of partition keys to copy, where min‑range‑value must be ≤ max‑range‑value. To specify a single partition key, min‑range‑value and max‑range‑value must be equal.
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.
force-split

Optional Boolean argument, specifies whether to split ROS containers if the range of partition keys spans multiple containers or part of a single container:

  • true: Split ROS containers as needed.

  • false (default): Return with an error if ROS containers must be split to implement this operation.

Privileges

Non-superuser, one of the following:

  • Owner of source and target tables

  • TRUNCATE (if force-split is true) and SELECT on the source table, INSERT on the target table

If the target table does not exist, you must also have CREATE privileges on the target schema to enable table creation.

Table attribute requirements

The following attributes of both tables must be identical:

  • Column definitions, including NULL/NOT NULL constraints

  • Segmentation

  • Partition clause

  • Number of projections

  • Shard count (Eon Mode only)

  • Projection sort order

  • Primary and unique key constraints. However, the key constraints do not have to be identically enabled. For more information on constraints, see Constraints.

  • Check constraints. For MOVE_PARTITIONS_TO_TABLE and COPY_PARTITIONS_TO_TABLE, Vertica enforces enabled check constraints on the target table only. For SWAP_PARTITIONS_BETWEEN_TABLES, Vertica enforces enabled check constraints on both tables. If there is a violation of an enabled check constraint, Vertica rolls back the operation.

  • Number and definitions of text indices.

Additionally, If access policies exist on the source table, the following must be true:

  • Access policies on both tables must be identical.

  • One of the following must be true:

    • The executing user owns the source table.

    • AccessPolicyManagementSuperuserOnly is set to true. See Managing access policies for details.

Table restrictions

The following restrictions apply to the source and target tables:

  • If the source and target partitions are in different storage tiers, Vertica returns a warning but the operation proceeds. The partitions remain in their existing storage tier.

  • The target table cannot be immutable.

  • The following tables cannot be used as sources or targets:

    • Temporary tables

    • Virtual tables

    • System tables

    • External 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)

See also

Archiving partitions