diff options
Diffstat (limited to 'doc/src/sgml/man7/ALTER_FOREIGN_TABLE.7')
| -rw-r--r-- | doc/src/sgml/man7/ALTER_FOREIGN_TABLE.7 | 377 |
1 files changed, 377 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/ALTER_FOREIGN_TABLE.7 b/doc/src/sgml/man7/ALTER_FOREIGN_TABLE.7 new file mode 100644 index 0000000..49c2a9c --- /dev/null +++ b/doc/src/sgml/man7/ALTER_FOREIGN_TABLE.7 @@ -0,0 +1,377 @@ +'\" t +.\" Title: ALTER FOREIGN TABLE +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "ALTER FOREIGN TABLE" "7" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * 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 WITHOUT OIDS + INHERIT \fIparent_table\fR + NO INHERIT \fIparent_table\fR + OWNER TO { \fInew_owner\fR | CURRENT_ROLE | 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 +\fBCREATE FOREIGN TABLE\fR\&. 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 +operations\&. See the similar form of +\fBALTER TABLE\fR +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 +\fBALTER TABLE\fR +for more details\&. +.RE +.PP +SET STORAGE +.RS 4 +This form sets the storage mode for a column\&. See the similar form of +\fBALTER TABLE\fR +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 +\fBCREATE FOREIGN TABLE\fR\&. 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 +\fBCREATE FOREIGN TABLE\fR\&.) 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 +\fBALTER TABLE\fR +for more details\&. +.RE +.PP +SET WITHOUT OIDS +.RS 4 +Backward compatibility syntax for removing the +oid +system column\&. As +oid +system columns cannot be added anymore, this never has an effect\&. +.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 +\fBALTER TABLE\fR +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), and in turn all objects that depend on those objects (see +Section\ \&5.14)\&. +.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 +\fBCREATE FOREIGN TABLE\fR +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); +.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(7)), DROP FOREIGN TABLE (\fBDROP_FOREIGN_TABLE\fR(7)) |