COPY_TABLE

Copies one table to another. This lightweight, in-memory copy increases performance by initially sharing the same storage between two tables. The copied table includes copies of any explicitly created projections from the source table. Once copied, the source and copy tables are independent of each other. Users can perform operations on one table without impacting the other. These operations can increase the overall storage required for both tables.

Creating multiple, concurrent copies of the same table may cause some of the copies to fail. To ensure success, copy tables sequentially.

Note: Although they share storage space, Vertica considers the tables as discrete objects for license capacity purposes. For example, copying a one TB table would only consume one TB of space. Your Vertica license, however, considers them as separate objects consuming two TB of space.

Syntax

COPY_TABLE (
    '[schema.]source-table',
    '[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 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

Table Attribute Requirements

The following attributes of both tables must be identical:

Table Restrictions

The following restrictions apply to the source and target tables:

Examples

If you call COPY_TABLE and the target table does not exist, the function creates the table automatically. In the following example, the target table public.newtable does not exist. COPY_TABLE creates the table and replicates the source table. Vertica also copies all the constraints associated with the source table 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