D7net
Home
Console
Upload
information
Create File
Create Folder
About
Tools
:
/
proc
/
self
/
root
/
opt
/
td-agent
/
embedded
/
share
/
man
/
man7
/
Filename :
ALTER_FOREIGN_TABLE.7
back
Copy
'\" t .\" Title: ALTER FOREIGN TABLE .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.76.1 <http://docbook.sf.net/> .\" Date: 2014 .\" Manual: PostgreSQL 9.3.5 Documentation .\" Source: PostgreSQL 9.3.5 .\" Language: English .\" .TH "ALTER FOREIGN TABLE" "7" "2014" "PostgreSQL 9.3.5" "PostgreSQL 9.3.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 .\" ALTER FOREIGN TABLE .SH "SYNOPSIS" .sp .nf ALTER FOREIGN TABLE [ IF EXISTS ] \fIname\fR \fIaction\fR [, \&.\&.\&. ] ALTER FOREIGN TABLE [ IF EXISTS ] \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 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 OPTIONS ( [ ADD | SET | DROP ] \fIoption\fR [\*(Aq\fIvalue\fR\*(Aq] [, \&.\&.\&. ]) OWNER TO \fInew_owner\fR 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(7))\&. 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 IF EXISTS .RS 4 Do not throw an error if the foreign table does not exist\&. A notice is issued in this case\&. .RE .PP SET DATA TYPE .RS 4 This form changes the type of a column of a foreign table\&. .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(7) operations\&. See the similar form of ALTER TABLE (\fBALTER_TABLE\fR(7)) for more details\&. .RE .PP SET ( \fIattribute_option\fR = \fIvalue\fR [, \&.\&.\&. ] ), RESET ( \fIattribute_option\fR [, \&.\&.\&. ] ) .RS 4 This form sets or resets per\-attribute options\&. See the similar form of ALTER TABLE (\fBALTER_TABLE\fR(7)) for more details\&. .RE .PP OWNER .RS 4 This form changes the owner of the foreign table to the specified user\&. .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 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 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 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\&. .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 CASCADE .RS 4 Automatically drop objects that depend on the dropped column (for example, views referencing the column)\&. .RE .PP RESTRICT .RS 4 Refuse to drop the column if there are any dependent objects\&. This is the default behavior\&. .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 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(7)) 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\&.