Exporting Tables

Vertica function EXPORT_TABLES exports DDL for tables and related objects in the current database. The generated SQL includes all non-virtual objects to which you have access. You can use this SQL to recreate tables and related non-virtual objects on a different cluster.

You execute EXPORT_TABLES as follows:

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

Setting Export Operation Scope

The EXPORT_TABLES scope argument specifies the scope of the export operation:

To export... Set scope to...

All database objects to which you have access, including constraints.

Empty string (' ')

One or more named objects, such as tables or sequences in one or more schemas. You can optionally qualify the schema the name of the current database—for example, mydb.myschema.newtable.

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 you have access.

Table object's name and, optionally, its path:

'VMart.myschema'

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 exported SQL includes the following types of statements, depending on what is required to recreate the tables and any related objects (such as sequences):

  • CREATE SCHEMA
  • CREATE TABLE
  • ALTER TABLE (to add foreign key constraints)
  • CREATE SEQUENCE
  • PARTITION BY

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:

  • CREATE SCHEMA
  • CREATE TABLE
  • ALTER TABLE (to add foreign keys)
  • CREATE SEQUENCE

Exporting Indvidual Table Objects

EXPORT_TABLES can export one or more database table objects. For example, the following statement exports named sequence public.my_seq: 

=> 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