1ALTER FOREIGN TABLE(7) PostgreSQL 12.6 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 WITHOUT OIDS
39 INHERIT parent_table
40 NO INHERIT parent_table
41 OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
42 OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
43
45 ALTER FOREIGN TABLE changes the definition of an existing foreign
46 table. There are several subforms:
47 ADD COLUMN
49 This form adds a new column to the foreign table, using the same
50 syntax as CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)). Unlike
51 the case when adding a column to a regular table, nothing happens
52 to the underlying storage: this action simply declares that some
53 new column is now accessible through the foreign table.
54 DROP COLUMN [ IF EXISTS ]
56 This form drops a column from a foreign table. You will need to say
57 CASCADE if anything outside the table depends on the column; for
58 example, views. If IF EXISTS is specified and the column does not
59 exist, no error is thrown. In this case a notice is issued instead.
60 SET DATA TYPE
62 This form changes the type of a column of a foreign table. Again,
63 this has no effect on any underlying storage: this action simply
64 changes the type that PostgreSQL believes the column to have.
65 SET/DROP DEFAULT
67 These forms set or remove the default value for a column. Default
68 values only apply in subsequent INSERT or UPDATE commands; they do
69 not cause rows already in the table to change.
70 SET/DROP NOT NULL
72 Mark a column as allowing, or not allowing, null values.
73 SET STATISTICS
75 This form sets the per-column statistics-gathering target for
76 subsequent ANALYZE(7) operations. See the similar form of ALTER
77 TABLE (ALTER_TABLE(7)) for more details.
78 SET ( attribute_option = value [, ... ] )
80 RESET ( attribute_option [, ... ] )
81 This form sets or resets per-attribute options. See the similar
82 form of ALTER TABLE (ALTER_TABLE(7)) for more details.
83 SET STORAGE
85 This form sets the storage mode for a column. See the similar form
86 of ALTER TABLE (ALTER_TABLE(7)) for more details. Note that the
87 storage mode has no effect unless the table's foreign-data wrapper
88 chooses to pay attention to it.
89 ADD table_constraint [ NOT VALID ]
91 This form adds a new constraint to a foreign table, using the same
92 syntax as CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)). Currently
93 only CHECK constraints are supported.
94 Unlike the case when adding a constraint to a regular table,
96 nothing is done to verify the constraint is correct; rather, this
97 action simply declares that some new condition should be assumed to
98 hold for all rows in the foreign table. (See the discussion in
99 CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).) If the constraint
100 is marked NOT VALID, then it isn't assumed to hold, but is only
101 recorded for possible future use.
102 VALIDATE CONSTRAINT
104 This form marks as valid a constraint that was previously marked as
105 NOT VALID. No action is taken to verify the constraint, but future
106 queries will assume that it holds.
107 DROP CONSTRAINT [ IF EXISTS ]
109 This form drops the specified constraint on a foreign table. If IF
110 EXISTS is specified and the constraint does not exist, no error is
111 thrown. In this case a notice is issued instead.
112 DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
114 These forms configure the firing of trigger(s) belonging to the
115 foreign table. See the similar form of ALTER TABLE (ALTER_TABLE(7))
116 for more details.
117 SET WITHOUT OIDS
119 Backward compatibility syntax for removing the oid system column.
120 As oid system columns cannot be added anymore, this never has an
121 effect.
122 INHERIT parent_table
124 This form adds the target foreign table as a new child of the
125 specified parent table. See the similar form of ALTER TABLE
126 (ALTER_TABLE(7)) for more details.
127 NO INHERIT parent_table
129 This form removes the target foreign table from the list of
130 children of the specified parent table.
131 OWNER
133 This form changes the owner of the foreign table to the specified
134 user.
135 OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
137 Change options for the foreign table or one of its columns. ADD,
138 SET, and DROP specify the action to be performed. ADD is assumed
139 if no operation is explicitly specified. Duplicate option names are
140 not allowed (although it's OK for a table option and a column
141 option to have the same name). Option names and values are also
142 validated using the foreign data wrapper library.
143 RENAME
145 The RENAME forms change the name of a foreign table or the name of
146 an individual column in a foreign table.
147 SET SCHEMA
149 This form moves the foreign table into another schema.
150 All the actions except RENAME and SET SCHEMA can be combined into a
152 list of multiple alterations to apply in parallel. For example, it is
153 possible to add several columns and/or alter the type of several
154 columns in a single command.
155 If the command is written as ALTER FOREIGN TABLE IF EXISTS ... and the
157 foreign table does not exist, no error is thrown. A notice is issued in
158 this case.
159 You must own the table to use ALTER FOREIGN TABLE. To change the schema
161 of a foreign table, you must also have CREATE privilege on the new
162 schema. To alter the owner, you must also be a direct or indirect
163 member of the new owning role, and that role must have CREATE privilege
164 on the table's schema. (These restrictions enforce that altering the
165 owner doesn't do anything you couldn't do by dropping and recreating
166 the table. However, a superuser can alter ownership of any table
167 anyway.) To add a column or alter a column type, you must also have
168 USAGE privilege on the data type.
169
171 name
172 The name (possibly schema-qualified) of an existing foreign table
173 to alter. If ONLY is specified before the table name, only that
174 table is altered. If ONLY is not specified, the table and all its
175 descendant tables (if any) are altered. Optionally, * can be
176 specified after the table name to explicitly indicate that
177 descendant tables are included.
178 column_name
180 Name of a new or existing column.
181 new_column_name
183 New name for an existing column.
184 new_name
186 New name for the table.
187 data_type
189 Data type of the new column, or new data type for an existing
190 column.
191 table_constraint
193 New table constraint for the foreign table.
194 constraint_name
196 Name of an existing constraint to drop.
197 CASCADE
199 Automatically drop objects that depend on the dropped column or
200 constraint (for example, views referencing the column), and in turn
201 all objects that depend on those objects (see Section 5.14).
202 RESTRICT
204 Refuse to drop the column or constraint if there are any dependent
205 objects. This is the default behavior.
206 trigger_name
208 Name of a single trigger to disable or enable.
209 ALL
211 Disable or enable all triggers belonging to the foreign table.
212 (This requires superuser privilege if any of the triggers are
213 internally generated triggers. The core system does not add such
214 triggers to foreign tables, but add-on code could do so.)
215 USER
217 Disable or enable all triggers belonging to the foreign table
218 except for internally generated triggers.
219 parent_table
221 A parent table to associate or de-associate with this foreign
222 table.
223 new_owner
225 The user name of the new owner of the table.
226 new_schema
228 The name of the schema to which the table will be moved.
229
231 The key word COLUMN is noise and can be omitted.
232 Consistency with the foreign server is not checked when a column is
234 added or removed with ADD COLUMN or DROP COLUMN, a NOT NULL or CHECK
235 constraint is added, or a column type is changed with SET DATA TYPE. It
236 is the user's responsibility to ensure that the table definition
237 matches the remote side.
238 Refer to CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)) for a further
240 description of valid parameters.
241
243 To mark a column as not-null:
244 ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
246 To change options of a foreign table:
248 ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');
250
252 The forms ADD, DROP, and SET DATA TYPE conform with the SQL standard.
253 The other forms are PostgreSQL extensions of the SQL standard. Also,
254 the ability to specify more than one manipulation in a single ALTER
255 FOREIGN TABLE command is an extension.
256 ALTER FOREIGN TABLE DROP COLUMN can be used to drop the only column of
258 a foreign table, leaving a zero-column table. This is an extension of
259 SQL, which disallows zero-column foreign tables.
260
262 CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)), DROP FOREIGN TABLE
263 (DROP_FOREIGN_TABLE(7))
264PostgreSQL 12.6 2021 ALTER FOREIGN TABLE(7)