ALTER FOREIGN TABLE
ALTER FOREIGN TABLE — Change the Definition of a Foreign Table
Synopsis
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME [ COLUMN ] column_name TO new_column_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
RENAME TO new_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
SET SCHEMA new_schema
where action is one of:
ADD [ COLUMN ] column_name data_type [ COLLATE collation ]
[ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED |
MAIN }
ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value']
[, ... ])
ADD table_constraint [ NOT VALID ]
VALIDATE CONSTRAINT constraint_name
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
SET WITHOUT OIDS
INHERIT parent_table
NO INHERIT parent_table
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
Description
ALTER FOREIGN TABLE changes the definition of an existing foreign table. There are several sub-forms:
ADD COLUMN
This form adds a new column to the foreign table using the same syntax as CREATE FOREIGN TABLE. Unlike adding a column to a regular table, this form does not affect the underlying storage: this action simply declares that a new column can be accessed through the foreign table.
ALTER FOREIGN TABLE
DROP COLUMN [ IF EXISTS ]
This form drops a column from a foreign table. If anything outside the table depends on the column, you will need to specify CASCADE; a typical example is views. If IF EXISTS is specified and the column does not exist, no error is thrown. In this case, a notice is issued instead.
SET DATA TYPE
This command changes the type of a column in a foreign table. The command does not affect the underlying storage.
SET/DROP DEFAULT
These forms set or remove the default value for a column. Default values only apply to subsequent INSERT or UPDATE commands; they do not cause existing rows in the table to be changed.
SET/DROP NOT NULL
Marks a column as allowing or not allowing null values.
SET STATISTICS
This form sets the per-column statistics collection target for subsequent ANALYZE operations. See the similar form of ALTER TABLE for details.
SET ( attribute_option = value [, ... ] )
RESET ( attribute_option [, ... ] )
This form sets or resets per-attribute options. See the similar form of ALTER TABLE for details.
SET STORAGE — This form sets the storage mode for a column. See the similar form in ALTER TABLE. Note that the storage mode has no effect unless the table's foreign data wrapper chooses to handle it.
ADD table_constraint [ NOT VALID ]
This form adds a new constraint to the foreign table using the same syntax as in CREATE FOREIGN TABLE. Currently, only CHECK constraints are supported. Unlike adding a constraint to a regular table, adding a constraint to a foreign table does not do anything to verify that the constraint is valid. This action simply declares a new condition that all rows in the foreign table should satisfy (see the discussion in CREATE FOREIGN TABLE). If the constraint is marked as NOT VALID, it is not assumed to be valid, but is merely recorded for future use.
VALIDATE CONSTRAINT
This form marks a constraint that was previously marked as NOT VALID as valid. No action is taken to verify the constraint, but future queries will assume that the constraint holds.
DROP CONSTRAINT [ IF EXISTS ]
This form drops the specified constraint on a foreign table. If IF EXISTS is specified but the constraint does not exist, no error is thrown. In this case, a notice is issued.
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
These forms configure the firing of triggers belonging to the foreign table. See the similar forms of ALTER TABLE for details.
SET WITHOUT OIDS
Backward-compatible syntax for removing the oid system column. Since the oid system column can no longer be added to foreign tables, this statement no longer has any effect.
INHERIT parent_table
This form makes the target foreign table a new child of the specified parent table. See the similar form of ALTER TABLE for details.
NO INHERIT parent_table
This form removes the target foreign table from the children list of the specified parent table.
OWNER
This form changes the owner of the foreign table to the specified user.
OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
Changes options for the foreign table or one of its columns. ADD, SET, and DROP specify the action to perform. If no operation is explicitly specified, ADD is assumed. Duplicate names are not allowed (however, a table option and a column option may share the same name). Option names and values will also be validated by the foreign data wrapper library.
RENAME
The RENAME form changes the name of a foreign table or a column within a foreign table.
SET SCHEMA
This form moves the foreign table to another schema.
All actions except RENAME and SET SCHEMA can be combined into a single list of multiple modifications to be applied in parallel.
For example, it is possible to add several columns and/or change the type of several columns in a single command.
If the command is written as ALTER FOREIGN TABLE IF EXISTS ... and the foreign table does not exist, no error is thrown. In this case, a notice is issued.
You must own the table to use ALTER FOREIGN TABLE. To change the schema of a foreign table, you must also have CREATE privilege on the new schema. To change 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 table's schema (these restrictions enforce that changing the owner cannot do anything you couldn't do by dropping and recreating the table. However, a superuser can change ownership of any table). To add a column or change a column's type, you must also have USAGE privilege on the data type.
Parameters
name
The name (optionally schema-qualified) of an existing foreign table to modify. If ONLY is specified before the table name, only that table is modified. If ONLY is not specified, the table and all its descendant tables (if any) are modified. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.
column_name
The name of a new or existing column.
new_column_name
The new name for an existing column.
new_name
The new name of the table.
data_type
The data type of a new column, or the new data type for an existing column.
table_constraint
New table constraint for the foreign table.
constraint_name
Name of an existing constraint to drop.
CASCADE
Automatically drop objects that depend on the dropped column or constraint (for example, views referencing the column), and in turn all objects that depend on those objects.
RESTRICT
Refuse to drop the column or constraint if there are any dependent objects. This is the default behavior.
trigger_name
The name of a trigger to disable or enable.
ALL
Disable or enable all triggers belonging to the foreign table (if any trigger is an internally generated trigger, this requires superuser privileges. The core system does not add such triggers to foreign tables, but add-on code may do so.).
USER
Disable or enable all triggers belonging to the foreign table except internally generated triggers.
parent_table
The parent table to associate or disassociate with this foreign table.
new_owner
The user name of the new owner of the table.
new_schema
The name of the schema to which the table will be moved.
Notes
The keyword COLUMN is a noise word and can be omitted.
When adding or removing a column using ADD COLUMN or DROP COLUMN, adding a NOT NULL or CHECK constraint, or changing a column type with SET DATA TYPE, consistency with the foreign server is not checked. It is the user's responsibility to ensure that the table definition matches the remote side.
For further descriptions of valid parameters, see CREATE FOREIGN TABLE.
Examples
# To mark a column as not null:
ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
# To change options of a foreign table:
ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2
'value2', DROP opt3 'value3');