Restrictions for external tables

Certain restrictions apply to external tables.

The following restrictions apply:
  • You cannot update an external table.
  • You cannot delete or truncate an external table. However, you can alter and drop the table definition. Dropping an external table drops the table definition, but it does not delete the data file that is associated with the table. To drop an external table, use the DROP statement. You can select the rows in the table and insert rows into the table.
  • You cannot select from more than one external table at a time in a query or subquery. The system displays an error if you specify multiple external tables in a query or subquery or if you reference the same external table more than once in a query or subquery: 
    ERROR: Multiple external table references in a query not allowed

    Instead of specifying more than one external table, you can load the data into a non-external table and specify this table in the query or subquery. Another option is to move data from one external table to another, such as by using SELECT and INSERT statements.

  • You cannot use the nzload command to load data into an external table. To insert into an external table, use the INSERT command. When you insert data into an external table that references an existing data file, the system truncates the file before inserting the data.
  • You cannot use a union operation that involves two or more external tables.
  • Using the nzbackup command to back up external tables backs up the schema but not the data.
  • Host-side operations, such as selects and rowsetlimit user and group property interactions, are not supported for compressed external tables.
  • The DecimalDelim option is not supported for compressed external tables.
  • The limit of concurrent loads is 300.
  • You cannot use external tables in complex SQL statements. If the statement is not supported, the system displays an error.