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