1ALTER FOREIGN TABLE(7) PostgreSQL 9.2.24 Documentation ALTER FOREIGN TABLE(7)
2
6 ALTER_FOREIGN_TABLE - change the definition of a foreign table
7
9 ALTER FOREIGN TABLE [ IF EXISTS ] name
10 action [, ... ]
11 ALTER FOREIGN TABLE [ IF EXISTS ] name
12 RENAME [ COLUMN ] column_name TO new_column_name
13 ALTER FOREIGN TABLE [ IF EXISTS ] name
14 RENAME TO new_name
15 ALTER FOREIGN TABLE [ IF EXISTS ] name
16 SET SCHEMA new_schema
17 where action is one of:
19 ADD [ COLUMN ] column_name data_type [ NULL | NOT NULL ]
21 DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
22 ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
23 ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
24 ALTER [ COLUMN ] column_name SET STATISTICS integer
25 ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
26 ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
27 ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
28 OWNER TO new_owner
29 OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
30
32 ALTER FOREIGN TABLE changes the definition of an existing foreign
33 table. There are several subforms:
34 ADD COLUMN
36 This form adds a new column to the foreign table, using the same
37 syntax as CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).
38 DROP COLUMN [ IF EXISTS ]
40 This form drops a column from a foreign table. You will need to say
41 CASCADE if anything outside the table depends on the column; for
42 example, views. If IF EXISTS is specified and the column does not
43 exist, no error is thrown. In this case a notice is issued instead.
44 IF EXISTS
46 Do not throw an error if the foreign table does not exist. A notice
47 is issued in this case.
48 SET DATA TYPE
50 This form changes the type of a column of a foreign table.
51 SET/DROP NOT NULL
53 Mark a column as allowing, or not allowing, null values.
54 SET STATISTICS
56 This form sets the per-column statistics-gathering target for
57 subsequent ANALYZE(7) operations. See the similar form of ALTER
58 TABLE (ALTER_TABLE(7)) for more details.
59 SET ( attribute_option = value [, ... ] ), RESET ( attribute_option [,
61 ... ] )
62 This form sets or resets per-attribute options. See the similar
63 form of ALTER TABLE (ALTER_TABLE(7)) for more details.
64 OWNER
66 This form changes the owner of the foreign table to the specified
67 user.
68 RENAME
70 The RENAME forms change the name of a foreign table or the name of
71 an individual column in a foreign table.
72 SET SCHEMA
74 This form moves the foreign table into another schema.
75 OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
77 Change options for the foreign table or one of its columns. ADD,
78 SET, and DROP specify the action to be performed. ADD is assumed
79 if no operation is explicitly specified. Duplicate option names are
80 not allowed (although it's OK for a table option and a column
81 option to have the same name). Option names and values are also
82 validated using the foreign data wrapper library.
83 All the actions except RENAME and SET SCHEMA can be combined into a
85 list of multiple alterations to apply in parallel. For example, it is
86 possible to add several columns and/or alter the type of several
87 columns in a single command.
88 You must own the table to use ALTER FOREIGN TABLE. To change the schema
90 of a foreign table, you must also have CREATE privilege on the new
91 schema. To alter the owner, you must also be a direct or indirect
92 member of the new owning role, and that role must have CREATE privilege
93 on the table's schema. (These restrictions enforce that altering the
94 owner doesn't do anything you couldn't do by dropping and recreating
95 the table. However, a superuser can alter ownership of any table
96 anyway.) To add a column or alter a column type, you must also have
97 USAGE privilege on the data type.
98
100 name
101 The name (possibly schema-qualified) of an existing foreign table
102 to alter.
103 column_name
105 Name of a new or existing column.
106 new_column_name
108 New name for an existing column.
109 new_name
111 New name for the table.
112 data_type
114 Data type of the new column, or new data type for an existing
115 column.
116 CASCADE
118 Automatically drop objects that depend on the dropped column (for
119 example, views referencing the column).
120 RESTRICT
122 Refuse to drop the column if there are any dependent objects. This
123 is the default behavior.
124 new_owner
126 The user name of the new owner of the table.
127 new_schema
129 The name of the schema to which the table will be moved.
130
132 The key word COLUMN is noise and can be omitted.
133 Consistency with the foreign server is not checked when a column is
135 added or removed with ADD COLUMN or DROP COLUMN, a NOT NULL constraint
136 is added, or a column type is changed with SET DATA TYPE. It is the
137 user's responsibility to ensure that the table definition matches the
138 remote side.
139 Refer to CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)) for a further
141 description of valid parameters.
142
144 To mark a column as not-null:
145 ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
147 To change options of a foreign table:
149 ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2, 'value2', DROP opt3 'value3');
151
153 The forms ADD, DROP, and SET DATA TYPE conform with the SQL standard.
154 The other forms are PostgreSQL extensions of the SQL standard. Also,
155 the ability to specify more than one manipulation in a single ALTER
156 FOREIGN TABLE command is an extension.
157 ALTER FOREIGN TABLE DROP COLUMN can be used to drop the only column of
159 a foreign table, leaving a zero-column table. This is an extension of
160 SQL, which disallows zero-column foreign tables.
161PostgreSQL 9.2.24 2017-11-06 ALTER FOREIGN TABLE(7)