Changes the definition of an external table.
ALTER EXTERNAL TABLE <name> <action> [, ... ]
where action is one of:
ADD [COLUMN] <new_column> <type>
DROP [COLUMN] <column> [RESTRICT|CASCADE]
ALTER [COLUMN] <column> TYPE <type> [USING <expression>]
OWNER TO <new_owner>
ALTER EXTERNAL TABLE
changes the definition of an existing external table. These are the supported ALTER EXTERNAL TABLE
actions:
CASCADE
keyword is required if anything outside the table depends on the column, such as a view that references the column.Use the ALTER TABLE command to perform these actions on an external table.
You must own the external table to use ALTER EXTERNAL TABLE
or ALTER 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.
Changes to the external table definition with either ALTER EXTERNAL TABLE
or ALTER TABLE
do not affect the external data.
The ALTER EXTERNAL TABLE
and ALTER TABLE
commands cannot modify the type external table (read, write, web), the table FORMAT
information, or the location of the external data. To modify this information, you must drop and recreate the external table definition.
Optional if an implicit or assignment cast exists from the old column data type to new data type. The clause is required if there is no implicit or assignment cast.
USING
clause does not affect the external data.
Add a new column to an external table definition:
ALTER EXTERNAL TABLE ext_expenses ADD COLUMN manager text;
Change the owner of an external table:
ALTER EXTERNAL TABLE ext_data OWNER TO jojo;
Change the data type of an external table:
ALTER EXTERNAL TABLE ext_leads ALTER COLUMN acct_code TYPE integer USING acct_code::integer
When altering the column data type from text
to integer
, the USING
clause is required but does not affect the external data.
ALTER EXTERNAL TABLE
is a Greenplum Database extension. There is no ALTER EXTERNAL TABLE
statement in the SQL standard or regular PostgreSQL.
CREATE EXTERNAL TABLE, DROP EXTERNAL TABLE, ALTER TABLE
Parent topic: SQL Command Reference