COPY_TABLE

Copies one table to another. This lightweight, in-memory function copies the DDL and all user-created projections from the source table. Projection statistics for the source table are also copied. Thus, the source and target tables initially have identical definitions and share the same storage.

After the copy operation is complete, the source and copy tables are independent of each other, so you can perform DML 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

Syntax

COPY_TABLE (
    '[[{namespace. | database. }]schema.]source-table',
    '[[{namespace. | database. }]schema.]target-table'
)

Parameters

{ namespace. | database. }

Name of the database or namespace that contains table:

• Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
• 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.

You cannot specify both a database and namespace name.

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 to copy. Vertica copies all data from this table to the target table.

target-table

The target table of the source table. If the target table already exists, Vertica appends the source to the existing table.

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.

Privileges

Non-superuser:

• Source table: SELECT

• Target schema/table (new): CREATE

• Target table (existing): INSERT

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.
  

  Note

  If the target table has primary or unique key constraints enabled and moving the partitions will insert duplicate key values into the target table, Vertica rolls back the operation. Enforcing constraints requires disk reads and can slow the copy process.

• Number and definitions of text indices.

• If the destination table already exists, the source and destination tables must have identical access policies.

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.
• If the source table contains a sequence, Vertica converts the sequence to an integer before copying it to the target table. If the target table contains IDENTITY or named sequence columns, Vertica cancels the copy and displays an error message.
• The following tables cannot be used as sources or targets:

  • Temporary tables

  • Virtual tables

  • System tables

  • External tables

Examples

If you call COPY_TABLE and the target table does not exist, the function creates the table automatically. In the following example, COPY_TABLE creates the target table public.newtable. Vertica also copies all the constraints associated with the source table public.product_dimension except foreign key constraints:

=> SELECT COPY_TABLE ( 'public.product_dimension', 'public.newtable');
-[ RECORD 1 ]--------------------------------------------------
copy_table | Created table public.newtable.
Copied table public.product_dimension to public.newtable

See also