'\" t .\" Title: ALTER FOREIGN TABLE .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.75.2 .\" Date: 2016 .\" Manual: PostgreSQL 9.5.2 Documentation .\" Source: PostgreSQL 9.5.2 .\" Language: English .\" .TH "ALTER FOREIGN TABLE" "5sql" "2016" "PostgreSQL 9.5.2" "PostgreSQL 9.5.2 Documentation" .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" ALTER_FOREIGN_TABLE \- change the definition of a foreign table .SH "SYNOPSIS" .sp .nf ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] \fIname\fR [ * ] \fIaction\fR [, \&.\&.\&. ] ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] \fIname\fR [ * ] RENAME [ COLUMN ] \fIcolumn_name\fR TO \fInew_column_name\fR ALTER FOREIGN TABLE [ IF EXISTS ] \fIname\fR RENAME TO \fInew_name\fR ALTER FOREIGN TABLE [ IF EXISTS ] \fIname\fR SET SCHEMA \fInew_schema\fR where \fIaction\fR is one of: ADD [ COLUMN ] \fIcolumn_name\fR \fIdata_type\fR [ COLLATE \fIcollation\fR ] [ \fIcolumn_constraint\fR [ \&.\&.\&. ] ] DROP [ COLUMN ] [ IF EXISTS ] \fIcolumn_name\fR [ RESTRICT | CASCADE ] ALTER [ COLUMN ] \fIcolumn_name\fR [ SET DATA ] TYPE \fIdata_type\fR [ COLLATE \fIcollation\fR ] ALTER [ COLUMN ] \fIcolumn_name\fR SET DEFAULT \fIexpression\fR ALTER [ COLUMN ] \fIcolumn_name\fR DROP DEFAULT ALTER [ COLUMN ] \fIcolumn_name\fR { SET | DROP } NOT NULL ALTER [ COLUMN ] \fIcolumn_name\fR SET STATISTICS \fIinteger\fR ALTER [ COLUMN ] \fIcolumn_name\fR SET ( \fIattribute_option\fR = \fIvalue\fR [, \&.\&.\&. ] ) ALTER [ COLUMN ] \fIcolumn_name\fR RESET ( \fIattribute_option\fR [, \&.\&.\&. ] ) ALTER [ COLUMN ] \fIcolumn_name\fR SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ALTER [ COLUMN ] \fIcolumn_name\fR OPTIONS ( [ ADD | SET | DROP ] \fIoption\fR [\(aq\fIvalue\fR\(aq] [, \&.\&.\&. ]) ADD \fItable_constraint\fR [ NOT VALID ] VALIDATE CONSTRAINT \fIconstraint_name\fR DROP CONSTRAINT [ IF EXISTS ] \fIconstraint_name\fR [ RESTRICT | CASCADE ] DISABLE TRIGGER [ \fItrigger_name\fR | ALL | USER ] ENABLE TRIGGER [ \fItrigger_name\fR | ALL | USER ] ENABLE REPLICA TRIGGER \fItrigger_name\fR ENABLE ALWAYS TRIGGER \fItrigger_name\fR SET WITH OIDS SET WITHOUT OIDS INHERIT \fIparent_table\fR NO INHERIT \fIparent_table\fR OWNER TO { \fInew_owner\fR | CURRENT_USER | SESSION_USER } OPTIONS ( [ ADD | SET | DROP ] \fIoption\fR [\(aq\fIvalue\fR\(aq] [, \&.\&.\&. ]) .fi .SH "DESCRIPTION" .PP \fBALTER FOREIGN TABLE\fR changes the definition of an existing foreign table\&. There are several subforms: .PP ADD COLUMN .RS 4 This form adds a new column to the foreign table, using the same syntax as CREATE FOREIGN TABLE (\fBCREATE_FOREIGN_TABLE\fR(5))\&. Unlike the case when adding a column to a regular table, nothing happens to the underlying storage: this action simply declares that some new column is now accessible through the foreign table\&. .RE .PP DROP COLUMN [ IF EXISTS ] .RS 4 This form drops a column from a foreign table\&. You will need to say CASCADE if anything outside the table depends on the column; for example, views\&. If IF EXISTS is specified and the column does not exist, no error is thrown\&. In this case a notice is issued instead\&. .RE .PP SET DATA TYPE .RS 4 This form changes the type of a column of a foreign table\&. Again, this has no effect on any underlying storage: this action simply changes the type that PostgreSQL believes the column to have\&. .RE .PP SET/DROP DEFAULT .RS 4 These forms set or remove the default value for a column\&. Default values only apply in subsequent \fBINSERT\fR or \fBUPDATE\fR commands; they do not cause rows already in the table to change\&. .RE .PP SET/DROP NOT NULL .RS 4 Mark a column as allowing, or not allowing, null values\&. .RE .PP SET STATISTICS .RS 4 This form sets the per\-column statistics\-gathering target for subsequent \fBANALYZE\fR(5) operations\&. See the similar form of ALTER TABLE (\fBALTER_TABLE\fR(5)) for more details\&. .RE .PP SET ( \fIattribute_option\fR = \fIvalue\fR [, \&.\&.\&. ] ) .br RESET ( \fIattribute_option\fR [, \&.\&.\&. ] ) .RS 4 This form sets or resets per\-attribute options\&. See the similar form of ALTER TABLE (\fBALTER_TABLE\fR(5)) for more details\&. .RE .PP SET STORAGE .RS 4 This form sets the storage mode for a column\&. See the similar form of ALTER TABLE (\fBALTER_TABLE\fR(5)) for more details\&. Note that the storage mode has no effect unless the table\(aqs foreign\-data wrapper chooses to pay attention to it\&. .RE .PP ADD \fItable_constraint\fR [ NOT VALID ] .RS 4 This form adds a new constraint to a foreign table, using the same syntax as CREATE FOREIGN TABLE (\fBCREATE_FOREIGN_TABLE\fR(5))\&. Currently only CHECK constraints are supported\&. .sp Unlike the case when adding a constraint to a regular table, nothing is done to verify the constraint is correct; rather, this action simply declares that some new condition should be assumed to hold for all rows in the foreign table\&. (See the discussion in CREATE FOREIGN TABLE (\fBCREATE_FOREIGN_TABLE\fR(5))\&.) If the constraint is marked NOT VALID, then it isn\(aqt assumed to hold, but is only recorded for possible future use\&. .RE .PP VALIDATE CONSTRAINT .RS 4 This form marks as valid a constraint that was previously marked as NOT VALID\&. No action is taken to verify the constraint, but future queries will assume that it holds\&. .RE .PP DROP CONSTRAINT [ IF EXISTS ] .RS 4 This form drops the specified constraint on a foreign table\&. If IF EXISTS is specified and the constraint does not exist, no error is thrown\&. In this case a notice is issued instead\&. .RE .PP DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER .RS 4 These forms configure the firing of trigger(s) belonging to the foreign table\&. See the similar form of ALTER TABLE (\fBALTER_TABLE\fR(5)) for more details\&. .RE .PP SET WITH OIDS .RS 4 This form adds an oid system column to the table (see Section 5.4, \(lqSystem Columns\(rq, in the documentation)\&. It does nothing if the table already has OIDs\&. Unless the table\(aqs foreign\-data wrapper supports OIDs, this column will simply read as zeroes\&. .sp Note that this is not equivalent to ADD COLUMN oid oid; that would add a normal column that happened to be named oid, not a system column\&. .RE .PP SET WITHOUT OIDS .RS 4 This form removes the oid system column from the table\&. This is exactly equivalent to DROP COLUMN oid RESTRICT, except that it will not complain if there is already no oid column\&. .RE .PP INHERIT \fIparent_table\fR .RS 4 This form adds the target foreign table as a new child of the specified parent table\&. See the similar form of ALTER TABLE (\fBALTER_TABLE\fR(5)) for more details\&. .RE .PP NO INHERIT \fIparent_table\fR .RS 4 This form removes the target foreign table from the list of children of the specified parent table\&. .RE .PP OWNER .RS 4 This form changes the owner of the foreign table to the specified user\&. .RE .PP OPTIONS ( [ ADD | SET | DROP ] \fIoption\fR [\(aq\fIvalue\fR\(aq] [, \&.\&.\&. ] ) .RS 4 Change options for the foreign table or one of its columns\&. ADD, SET, and DROP specify the action to be performed\&. ADD is assumed if no operation is explicitly specified\&. Duplicate option names are not allowed (although it\(aqs OK for a table option and a column option to have the same name)\&. Option names and values are also validated using the foreign data wrapper library\&. .RE .PP RENAME .RS 4 The RENAME forms change the name of a foreign table or the name of an individual column in a foreign table\&. .RE .PP SET SCHEMA .RS 4 This form moves the foreign table into another schema\&. .RE .PP 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\&. .PP 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\&. .PP You must own the table to use \fBALTER FOREIGN TABLE\fR\&. To change the schema of a foreign 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 table\(aqs schema\&. (These restrictions enforce that altering the owner doesn\(aqt do anything you couldn\(aqt 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\&. .SH "PARAMETERS" .PP \fIname\fR .RS 4 The name (possibly schema\-qualified) of an existing foreign table to alter\&. If ONLY is specified before the table name, only that table is altered\&. If ONLY is not specified, the table and all its descendant tables (if any) are altered\&. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included\&. .RE .PP \fIcolumn_name\fR .RS 4 Name of a new or existing column\&. .RE .PP \fInew_column_name\fR .RS 4 New name for an existing column\&. .RE .PP \fInew_name\fR .RS 4 New name for the table\&. .RE .PP \fIdata_type\fR .RS 4 Data type of the new column, or new data type for an existing column\&. .RE .PP \fItable_constraint\fR .RS 4 New table constraint for the foreign table\&. .RE .PP \fIconstraint_name\fR .RS 4 Name of an existing constraint to drop\&. .RE .PP CASCADE .RS 4 Automatically drop objects that depend on the dropped column or constraint (for example, views referencing the column)\&. .RE .PP RESTRICT .RS 4 Refuse to drop the column or constraint if there are any dependent objects\&. This is the default behavior\&. .RE .PP \fItrigger_name\fR .RS 4 Name of a single trigger to disable or enable\&. .RE .PP ALL .RS 4 Disable or enable all triggers belonging to the foreign table\&. (This requires superuser privilege if any of the triggers are internally generated triggers\&. The core system does not add such triggers to foreign tables, but add\-on code could do so\&.) .RE .PP USER .RS 4 Disable or enable all triggers belonging to the foreign table except for internally generated triggers\&. .RE .PP \fIparent_table\fR .RS 4 A parent table to associate or de\-associate with this foreign table\&. .RE .PP \fInew_owner\fR .RS 4 The user name of the new owner of the table\&. .RE .PP \fInew_schema\fR .RS 4 The name of the schema to which the table will be moved\&. .RE .SH "NOTES" .PP The key word COLUMN is noise and can be omitted\&. .PP 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\(aqs responsibility to ensure that the table definition matches the remote side\&. .PP Refer to CREATE FOREIGN TABLE (\fBCREATE_FOREIGN_TABLE\fR(5)) for a further description of valid parameters\&. .SH "EXAMPLES" .PP To mark a column as not\-null: .sp .if n \{\ .RS 4 .\} .nf ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL; .fi .if n \{\ .RE .\} .PP To change options of a foreign table: .sp .if n \{\ .RS 4 .\} .nf ALTER FOREIGN TABLE myschema\&.distributors OPTIONS (ADD opt1 \(aqvalue\(aq, SET opt2 \(aqvalue2\(aq, DROP opt3 \(aqvalue3\(aq); .fi .if n \{\ .RE .\} .SH "COMPATIBILITY" .PP 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 \fBALTER FOREIGN TABLE\fR command is an extension\&. .PP \fBALTER FOREIGN TABLE DROP COLUMN\fR 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\&. .SH "SEE ALSO" CREATE FOREIGN TABLE (\fBCREATE_FOREIGN_TABLE\fR(5)), DROP FOREIGN TABLE (\fBDROP_FOREIGN_TABLE\fR(5))