1ALTER FOREIGN TABLE(7) PostgreSQL 11.3 Documentation ALTER FOREIGN TABLE(7)
2
6 ALTER_FOREIGN_TABLE - change the definition of a foreign table
7
9 ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
10 action [, ... ]
11 ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] 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 [ COLLATE collation ] [ column_constraint [ ... ] ]
21 DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
22 ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ]
23 ALTER [ COLUMN ] column_name SET DEFAULT expression
24 ALTER [ COLUMN ] column_name DROP DEFAULT
25 ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
26 ALTER [ COLUMN ] column_name SET STATISTICS integer
27 ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
28 ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
29 ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
30 ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
31 ADD table_constraint [ NOT VALID ]
32 VALIDATE CONSTRAINT constraint_name
33 DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
34 DISABLE TRIGGER [ trigger_name | ALL | USER ]
35 ENABLE TRIGGER [ trigger_name | ALL | USER ]
36 ENABLE REPLICA TRIGGER trigger_name
37 ENABLE ALWAYS TRIGGER trigger_name
38 SET WITH OIDS
39 SET WITHOUT OIDS
40 INHERIT parent_table
41 NO INHERIT parent_table
42 OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
43 OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
44
46 ALTER FOREIGN TABLE changes the definition of an existing foreign
47 table. There are several subforms:
48 ADD COLUMN
50 This form adds a new column to the foreign table, using the same
51 syntax as CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)). Unlike
52 the case when adding a column to a regular table, nothing happens
53 to the underlying storage: this action simply declares that some
54 new column is now accessible through the foreign table.
55 DROP COLUMN [ IF EXISTS ]
57 This form drops a column from a foreign table. You will need to say
58 CASCADE if anything outside the table depends on the column; for
59 example, views. If IF EXISTS is specified and the column does not
60 exist, no error is thrown. In this case a notice is issued instead.
61 SET DATA TYPE
63 This form changes the type of a column of a foreign table. Again,
64 this has no effect on any underlying storage: this action simply
65 changes the type that PostgreSQL believes the column to have.
66 SET/DROP DEFAULT
68 These forms set or remove the default value for a column. Default
69 values only apply in subsequent INSERT or UPDATE commands; they do
70 not cause rows already in the table to change.
71 SET/DROP NOT NULL
73 Mark a column as allowing, or not allowing, null values.
74 SET STATISTICS
76 This form sets the per-column statistics-gathering target for
77 subsequent ANALYZE(7) operations. See the similar form of ALTER
78 TABLE (ALTER_TABLE(7)) for more details.
79 SET ( attribute_option = value [, ... ] )
81 RESET ( attribute_option [, ... ] )
82 This form sets or resets per-attribute options. See the similar
83 form of ALTER TABLE (ALTER_TABLE(7)) for more details.
84 SET STORAGE
86 This form sets the storage mode for a column. See the similar form
87 of ALTER TABLE (ALTER_TABLE(7)) for more details. Note that the
88 storage mode has no effect unless the table's foreign-data wrapper
89 chooses to pay attention to it.
90 ADD table_constraint [ NOT VALID ]
92 This form adds a new constraint to a foreign table, using the same
93 syntax as CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)). Currently
94 only CHECK constraints are supported.
95 Unlike the case when adding a constraint to a regular table,
97 nothing is done to verify the constraint is correct; rather, this
98 action simply declares that some new condition should be assumed to
99 hold for all rows in the foreign table. (See the discussion in
100 CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).) If the constraint
101 is marked NOT VALID, then it isn't assumed to hold, but is only
102 recorded for possible future use.
103 VALIDATE CONSTRAINT
105 This form marks as valid a constraint that was previously marked as
106 NOT VALID. No action is taken to verify the constraint, but future
107 queries will assume that it holds.
108 DROP CONSTRAINT [ IF EXISTS ]
110 This form drops the specified constraint on a foreign table. If IF
111 EXISTS is specified and the constraint does not exist, no error is
112 thrown. In this case a notice is issued instead.
113 DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
115 These forms configure the firing of trigger(s) belonging to the
116 foreign table. See the similar form of ALTER TABLE (ALTER_TABLE(7))
117 for more details.
118 SET WITH OIDS
120 This form adds an oid system column to the table (see Section 5.4).
121 It does nothing if the table already has OIDs. Unless the table's
122 foreign-data wrapper supports OIDs, this column will simply read as
123 zeroes.
124 Note that this is not equivalent to ADD COLUMN oid oid; that would
126 add a normal column that happened to be named oid, not a system
127 column.
128 SET WITHOUT OIDS
130 This form removes the oid system column from the table. This is
131 exactly equivalent to DROP COLUMN oid RESTRICT, except that it will
132 not complain if there is already no oid column.
133 INHERIT parent_table
135 This form adds the target foreign table as a new child of the
136 specified parent table. See the similar form of ALTER TABLE
137 (ALTER_TABLE(7)) for more details.
138 NO INHERIT parent_table
140 This form removes the target foreign table from the list of
141 children of the specified parent table.
142 OWNER
144 This form changes the owner of the foreign table to the specified
145 user.
146 OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
148 Change options for the foreign table or one of its columns. ADD,
149 SET, and DROP specify the action to be performed. ADD is assumed
150 if no operation is explicitly specified. Duplicate option names are
151 not allowed (although it's OK for a table option and a column
152 option to have the same name). Option names and values are also
153 validated using the foreign data wrapper library.
154 RENAME
156 The RENAME forms change the name of a foreign table or the name of
157 an individual column in a foreign table.
158 SET SCHEMA
160 This form moves the foreign table into another schema.
161 All the actions except RENAME and SET SCHEMA can be combined into a
163 list of multiple alterations to apply in parallel. For example, it is
164 possible to add several columns and/or alter the type of several
165 columns in a single command.
166 If the command is written as ALTER FOREIGN TABLE IF EXISTS ... and the
168 foreign table does not exist, no error is thrown. A notice is issued in
169 this case.
170 You must own the table to use ALTER FOREIGN TABLE. To change the schema
172 of a foreign table, you must also have CREATE privilege on the new
173 schema. To alter the owner, you must also be a direct or indirect
174 member of the new owning role, and that role must have CREATE privilege
175 on the table's schema. (These restrictions enforce that altering the
176 owner doesn't do anything you couldn't do by dropping and recreating
177 the table. However, a superuser can alter ownership of any table
178 anyway.) To add a column or alter a column type, you must also have
179 USAGE privilege on the data type.
180
182 name
183 The name (possibly schema-qualified) of an existing foreign table
184 to alter. If ONLY is specified before the table name, only that
185 table is altered. If ONLY is not specified, the table and all its
186 descendant tables (if any) are altered. Optionally, * can be
187 specified after the table name to explicitly indicate that
188 descendant tables are included.
189 column_name
191 Name of a new or existing column.
192 new_column_name
194 New name for an existing column.
195 new_name
197 New name for the table.
198 data_type
200 Data type of the new column, or new data type for an existing
201 column.
202 table_constraint
204 New table constraint for the foreign table.
205 constraint_name
207 Name of an existing constraint to drop.
208 CASCADE
210 Automatically drop objects that depend on the dropped column or
211 constraint (for example, views referencing the column), and in turn
212 all objects that depend on those objects (see Section 5.13).
213 RESTRICT
215 Refuse to drop the column or constraint if there are any dependent
216 objects. This is the default behavior.
217 trigger_name
219 Name of a single trigger to disable or enable.
220 ALL
222 Disable or enable all triggers belonging to the foreign table.
223 (This requires superuser privilege if any of the triggers are
224 internally generated triggers. The core system does not add such
225 triggers to foreign tables, but add-on code could do so.)
226 USER
228 Disable or enable all triggers belonging to the foreign table
229 except for internally generated triggers.
230 parent_table
232 A parent table to associate or de-associate with this foreign
233 table.
234 new_owner
236 The user name of the new owner of the table.
237 new_schema
239 The name of the schema to which the table will be moved.
240
242 The key word COLUMN is noise and can be omitted.
243 Consistency with the foreign server is not checked when a column is
245 added or removed with ADD COLUMN or DROP COLUMN, a NOT NULL or CHECK
246 constraint is added, or a column type is changed with SET DATA TYPE. It
247 is the user's responsibility to ensure that the table definition
248 matches the remote side.
249 Refer to CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)) for a further
251 description of valid parameters.
252
254 To mark a column as not-null:
255 ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
257 To change options of a foreign table:
259 ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');
261
263 The forms ADD, DROP, and SET DATA TYPE conform with the SQL standard.
264 The other forms are PostgreSQL extensions of the SQL standard. Also,
265 the ability to specify more than one manipulation in a single ALTER
266 FOREIGN TABLE command is an extension.
267 ALTER FOREIGN TABLE DROP COLUMN can be used to drop the only column of
269 a foreign table, leaving a zero-column table. This is an extension of
270 SQL, which disallows zero-column foreign tables.
271
273 CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)), DROP FOREIGN TABLE
274 (DROP_FOREIGN_TABLE(7))
275PostgreSQL 11.3 2019 ALTER FOREIGN TABLE(7)