SWAP_PARTITIONS_BETWEEN_TABLES
Swaps partitions between two tables.
Syntax
SWAP_PARTITIONS_BETWEEN_TABLES (
'[[database.]schema.]staging‑table',
'min‑range‑value',
'max‑range‑value',
'[[database.]schema.]target‑table'
[, force‑split]
)
Parameters
[database.]schema
|
Specifies a schema, by default myschema.thisDbObject If you specify a database, it must be the current database. |
| staging‑table |
The staging table from which to swap partitions. |
| min‑range‑value
max‑range‑value |
The minimum and maximum value of partition keys to swap, where min‑range‑value must be ≤ max‑range‑value. To swap one partition, min‑range‑value and max‑range‑value must be equal. |
| target‑table |
The table to which the partitions are to be swapped. The target table cannot be the same as the staging 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:
|
Privileges
One of the following:
- DBADMIN role
- Owner of both tables
- USAGE privileges on both schemas, and TRUNCATE and INSERT privileges on both tables.
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, 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_TABLEandCOPY_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.
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 Swapping Partitions.
