NAME¶

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 | DEFAULT }


    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_ROLE | 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 subforms:

ADD COLUMN

DROP COLUMN [ IF EXISTS ]

SET DATA TYPE

SET/DROP DEFAULT

SET/DROP NOT NULL

SET STATISTICS

SET ( attribute_option = value [, ... ] )
RESET ( attribute_option [, ... ] )

SET STORAGE

ADD table_constraint [ NOT VALID ]

VALIDATE CONSTRAINT

DROP CONSTRAINT [ IF EXISTS ]

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER

SET WITHOUT OIDS

INHERIT parent_table

NO INHERIT parent_table

OWNER

OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )

RENAME

SET SCHEMA

All the actions except RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns and/or alter 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. A notice is issued in this case.

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 alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.) To add a column or alter a column type, you must also have USAGE privilege on the data type.

PARAMETERS¶

name

column_name

new_column_name

new_name

data_type

table_constraint

constraint_name

CASCADE

RESTRICT

trigger_name

ALL

USER

parent_table

new_owner

new_schema

NOTES¶

The key word COLUMN is noise and can be omitted.

Consistency with the foreign server is not checked when a column is added or removed with ADD COLUMN or DROP COLUMN, a NOT NULL or CHECK constraint is added, or a column type is changed with SET DATA TYPE. It is the user's responsibility to ensure that the table definition matches the remote side.

Refer to CREATE FOREIGN TABLE for a further description of valid parameters.

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);

COMPATIBILITY¶

The forms ADD, DROP, and SET DATA TYPE conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER FOREIGN TABLE command is an extension.

ALTER FOREIGN TABLE DROP COLUMN can be used to drop the only column of a foreign table, leaving a zero-column table. This is an extension of SQL, which disallows zero-column foreign tables.

SEE ALSO¶

CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)), DROP FOREIGN TABLE (DROP_FOREIGN_TABLE(7))