ALTER EXTERNAL TABLE

Change the definition of an external table.

Summary

ALTER EXTERNAL TABLE name action [, ... ]

Among them, action is:

  ADD [COLUMN] new_column type
  DROP [COLUMN] column [RESTRICT|CASCADE]
  ALTER [COLUMN] column TYPE type
  OWNER TO new_owner

describe

ALTER EXTERNAL TABLE Changes the definition of an existing external table. The following are the actions supported by ALTER EXTERNAL TABLE.

ADD COLUMN — Add a new column to the definition of the external table.
DROP COLUMN — Delete a column from an external table definition. If you delete columns of read-only external tables, this will only be modified in the table definition of YMatrix. The CASCADE keyword is used when some other tables depend on these columns, for example, a view depends on this column.
ALTER COLUMN TYPE — Changes the data type of a table column.
OWNER — Change the owner of an external table to the specified user.

These operations can be implemented in an external table using ALTER TABLE.

Set (modify) table mode
Rename table
Rename columns in table

You must use ALTER EXTERNAL TABLE or ALTER TABLE when the owner of the external table is. To change the schema of an external table, you must also have CREATE permissions on the new schema. To change the owner, the user must also be a direct or indirect member of the newly owned role, which must have CREATE privileges for the schema of the external table. Superusers automatically have these permissions.

Modifying the definition of an external table with ALTER EXTERNAL TABLE or ALTER TABLE will not affect external data.

The ALTER EXTERNAL TABLE and ALTER TABLE commands do not modify the type of external table (read, write, web page), the FORMAT information of the table, or the location of external data. To modify this information, you must delete and rebuild the definition of the external table.

Parameters

name

The name of the existing external table definition to be modified (can be scheme-qualified).

column

The name of the existing column.

new_column

Name of the new column

type

The data type of the new column, or the new data type of the existing column.

new_owner

New owner role name for the external table.

CASCADE

Automatically delete objects that depend on the columns to be deleted, such as the view that depends on the columns to be deleted.

RESTRICT

Reject to delete columns with dependencies, this is the default setting.

Example

Add a new column to the definition of the external table:

ALTER EXTERNAL TABLE ext_expenses ADD COLUMN manager text;

Change the owner of the external table:

ALTER EXTERNAL TABLE ext_data OWNER TO jojo;

Change the data type of the external table:

ALTER EXTERNAL TABLE ext_leads ALTER COLUMN acct_code TYPE integer;

compatibility