MOVE_PARTITIONS_TO_TABLE

Moves partitions from one table to another.

Syntax

MOVE_PARTITIONS_TO_TABLE (
    '[[database.]schema.]source‑table',
    'min‑range‑value',
    'max‑range‑value',
    '[[database.]schema.]target-table'
     [, force‑split]
)

Parameters

[database.]schema	Specifies a schema, by default `public`. If schema is any schema other than `public`, you must supply the schema name. For example: myschema.thisDbObject If you specify a database, it must be the current database.
source‑table	The source table of the partitions to move.
min‑range‑value max‑range‑value	The minimum and maximum value of partition keys to move, where min‑range‑value must be ≤ max‑range‑value. To move one partition, min‑range‑value and max‑range‑value must be equal.
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 `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

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
Partition clause
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, see Constraints in the Administrator's Guide.
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 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.

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

MOVE_PARTITIONS_TO_TABLE returns an error if both of the following conditions are true of the source table:

The table's partition expression includes a GROUP BY clause—for example, hierarchically partitioned tables.
The table has projections that aggregate data—for example, live aggregate or Top-K projections.

Examples

See Archiving Partitions.