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.