Exporting Tables

Use the Vertica function EXPORT_TABLES to recreate one or more tables, and related non-virtual objects, on a different cluster. EXPORT_TABLES has the following syntax:

EXPORT_TABLES( [ 'destination'] [, 'scope' ] )

Setting Scope of Export

You can set the scope of the export operation to various levels:

To export... Use this scope...

All objects to which the user has access, including constraints.

An empty string (' ')

One or more named objects, such as tables or sequences in one or more schemas. You can optionally qualify the schema with a database prefix, myvertica.myschema.newtable.

A comma-delimited list of table objects. For example:

'myschema.newtable, yourschema.oldtable'

A named table object in the current search path. You can specify a schema, table, or sequence. If you specify a schema, EXPORT_TABLES exports all table objects in that schema to which the user has access.

The table object's name and, optionally, its path:

'VMart.myschema'

The SQL script includes only the non-virtual objects to which the current user has access.

Note: EXPORT_TABLES does not export views. If you specify a view name, Vertica silently ignores it and the view is omitted from the generated script. To export views, use EXPORT_OBJECTS.

Exporting All Table Objects

If you set the scope parameter to an empty string (''), Vertica exports all tables and their related objects:

 => SELECT EXPORT_TABLES(
      '/home/dbadmin/xtest/sql_tables_empty.sql',
      '');
            EXPORT_TABLES           
-------------------------------------
 Catalog data exported successfully
(1 row)

The SQL script includes the following types of statements, depending on what is required to recreate the tables and any related objects (such as sequences):

Exporting a List of Tables

Use EXPORT_TABLE with a comma-separated list of objects, including tables, views, or schemas:

=> SELECT EXPORT_TABLES(
     '/home/dbadmin/xtest/sql_tables_del.sql'
     'public.student, public.test7');
            EXPORT_TABLES            
-------------------------------------
 Catalog data exported successfully
(1 row)

The SQL script can include the following types of statements, depending on what is required to recreate the exported objects:

Exporting a Single Table Object

Use EXPORT_TABLES to export one or more database table objects.

This example exports a named sequence, my_seq, qualifying the sequence with the schema name (public):

=> SELECT EXPORT_TABLES(
      '/home/dbadmin/xtest/export_one_sequence.sql',
      'public.my_seq');
            EXPORT_TABLES            
-------------------------------------
 Catalog data exported successfully
(1 row)

Following are the contents of the export_one_sequence.sql output file using a more command:

$ more export_one_sequence.sql
 CREATE SEQUENCE public.my_seq ;

See Also

Exporting Objects