CREATE EXTERNAL TABLE command

You can use the CREATE EXTERNAL TABLE command to create external tables. An external table allows to treat an external file as a database table.

Privileges for creating external tables

To create an external table, you must have the CREATE EXTERNAL TABLE administration privilege and the List privilege on the database where you are defining the table. If the schema where you define the table is not the default schema, you must have the List privilege on the schema as well.

The database user who issues the CREATE EXTERNAL TABLE command owns the external table.

When you create an external table, you must specify the location where the external table data object is stored. The nz operating system user must have permission to read from the data object location to support SELECT operations against the table and to write to the location if commands such as INSERT are used to add rows to the external table.

Syntax

To create an external table that is based on another table, use the following syntax:

CREATE EXTERNAL TABLE table_name
SAMEAS table_name
USING external_table_options

To create an external table by defining columns, use the following syntax:

CREATE EXTERNAL TABLE table_name
({  column_name type
[ column_constraint [ ... ] ]} [, ... ]
)
[USING external_table_options]

For information about the values that you can specify for the external_table_options variable, see External table options.

Although you can specify the PRIMARY KEY, DEFAULT, UNIQUE, and REFERENCES options for column specifications for both the CREATE TABLE command and the CREATE EXTERNAL TABLE command, the UNIQUE, PRIMARY KEY, and REFERENCES options are ignored for the CREATE EXTERNAL TABLE command. No error is generated. Because constraint checks and referential integrity are not supported for external tables, be careful when inserting data into these tables.