ALTER EXTERNAL TABLE

Changes the definition of an external table.

Synopsis

ALTER EXTERNAL TABLE name RENAME [COLUMN] column TO new_column

ALTER EXTERNAL TABLE name RENAME TO new_name

ALTER EXTERNAL TABLE name SET SCHEMA new_schema

ALTER EXTERNAL TABLE name action [, ... ]

where action is one of:

  ADD [COLUMN] column_name type
  DROP [COLUMN] column
  ALTER [COLUMN] column
  TYPE type [USING expression]
  OWNER TO new_owner

Description

ALTER EXTERNAL TABLE changes the definition of an existing external table. There are several subforms:

ADD COLUMN — Adds a new column to the external table definition.
DROP COLUMN — Drops a column from the external table definition. Note that if you drop readable external table columns, it only changes the table definition in Greenplum Database. External data files are not changed.
ALTER COLUMN TYPE — Changes the data type of a column of a table. The optional USING clause specifies how to compute the new column value from the old. If omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from the old to new type.
OWNER — Changes the owner of the external table to the specified user.
RENAME — Changes the name of an external table or the name of an individual column in the table. There is no effect on the external data.
SET SCHEMA — Moves the external table into another schema.

You must own the external table to use ALTER EXTERNAL TABLE. To change the schema of an external table, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the external table's schema. A superuser has these privileges automatically.

In this release, ALTER EXTERNAL TABLE cannot modify the external table type, the data format, or the location of the external data. To modify this information, you must drop and recreate the external table definition.

Parameters

name: The name (possibly schema-qualified) of an existing external table definition to alter.
column: Name of a new or existing column.
new_column: New name for an existing column.
new_name: New name for the external table.
type: Data type of the new column, or new data type for an existing column.
new_owner: The role name of the new owner of the external table.
new_schema: The name of the schema to which the external table will be moved.

Examples

Add a new column to an external table definition:

ALTER EXTERNAL TABLE ext_expenses ADD COLUMN manager text;

Change the name of an external table:

ALTER EXTERNAL TABLE ext_data RENAME TO ext_sales_data;

Change the owner of an external table:

ALTER EXTERNAL TABLE ext_data OWNER TO jojo;

Change the schema of an external table:

ALTER EXTERNAL TABLE ext_leads SET SCHEMA marketing;

Compatibility

ALTER EXTERNAL TABLE is a Greenplum Database extension. There is no ALTER EXTERNAL TABLE statement in the SQL standard or regular PostgreSQL.