MOVE_PARTITIONS_TO_TABLE
Moves partitions from one table to another.
Syntax
MOVE_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 move. |
min‑range‑value | The minimum value of the partition to move. To move one partition, the minimum and maximum values must be the same. |
max‑range‑value | The maximum value of the partition to be moved. To move one partition, the minimum and maximum values must be the same. |
target‑table |
The target table of the partitions to move. If the table does not exist, Vertica creates a table from the source table's definition, by calling |
Privileges
If the target table does not exist, you must have CREATE privileges on the target schema, to enable table creation. One of the following conditions is also required:
- DBADMIN role
- Owner of the source and target tables
- USAGE privileges on source and target schemas,TRUNCATE privileges on the source table, and INSERT privileges on the target table
Table Attribute Requirements
The following attributes of both tables must be identical:
- Column definitions, including NULL/NOT NULL constraints
- Segmentation
- Partitioning expression
- Number of projections
- Projection sort order
- Primary and unique key constraints. However, the key constraints do not have to be identically enabled. For more information on constraints, refer to Enforcing Primary Key and Foreign Key Constraints.
Note: If the target table has primary or unique key constraints enabled and copying or moving the partitions will insert duplicate key values into the target table, Vertica rolls back the operation.
- Check constraints. For
MOVE_PARTITIONS_TO_TABLE
andCOPY_PARTITIONS_TO_TABLE
, Vertica enforces enabled check constraints on the target table only. ForSWAP_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.
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 following tables cannot be used as sources or targets:
- Temporary tables
- Virtual tables
- System tables
- External tables
Examples
See Archiving Partitions.