Skip to main content

ALTER EXTERNAL TABLE

Redefines an external table.

Syntax​

ALTER EXTERNAL TABLE <name> <action> [, ... ]
  where <action> can be:
  ADD [COLUMN] <new_column> <type>
  DROP [COLUMN] <column> [RESTRICT | CASCADE]
  ALTER [COLUMN] <column> TYPE <type>
  OWNER TO <new_owner>

Description​

To change the schema of an external table, you must be the external table owner and have the CREATE privilege on the new schema.

To change the owner of an external table, you must be the external table owner and have the direct or indirect membership in the new owning role. The new owning role must have the CREATE privilege on the schema to which the external table belongs.

To perform the following operations, you need to use ALTER TABLE instead:

  • Set or change the schema for an external table.
  • Rename an external table or a column in table.
  • Set or change the distribution policy for a writable external table.

ALTER EXTERNAL TABLE or ALTER TABLE has no impact on external data. If you want to change the type (read, write, or web) of an external table, the FORMAT information of a table, or the location of the external data, you must drop the external table and recreate one.

Parameters​

  • <name>

    The name of the external table. You can specify the name with the schema qualification.

  • <action>

    The action to perform on the external table. Supported actions include:

    • ADD [COLUMN]: adds a column.

      To perform this action, you must specify the <new_column> and <type>, where <new_column> indicates the name of the column that you want to add and <type> indicates the data type of the column.

    • DROP [COLUMN]: drops a column.

      To perform this action, you must specify the <column> which indicates the name of the column that you want to delete. If any database objects depend on the column, such as a view that references the column, the CASCADE keyword is required to drop the column.

    • ALTER [COLUMN] ... TYPE: changes the data type of a column.

      To perform this action, you must specify the <column> and <type>, where <column> indicates the name of the column that you want to modify and <type> indicates the new data type of the column.

    • OWNER TO: alters the table owner.

      To perform this action, you must specify the <new_owner> which indicates the new owner of the external table.

  • [CASCADE | RESTRICT]

    Used with the DROP COLUMN action to specify whether to drop the column if the column has dependent database objects. CASCADE drops all dependent objects along with the column. RESTRICT rejects the entire drop operation. RESTRICT is the default.

Examples​

Drop column age from external table students:

ALTER EXTERNAL TABLE students DROP COLUMN age;

Change the owner of external table students to [email protected]:

ALTER EXTERNAL TABLE students OWNER TO "[email protected]";

SQL standard compatibility​

The SQL standard or regular PostgreSQL does not support ALTER EXTERNAL TABLE. It is a Relyt extension.