1ALTER FOREIGN TABLE(7)   PostgreSQL 10.7 Documentation  ALTER FOREIGN TABLE(7)
2
3
4

NAME

6       ALTER_FOREIGN_TABLE - change the definition of a foreign table
7

SYNOPSIS

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
18       where action is one of:
19
20           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

DESCRIPTION

46       ALTER FOREIGN TABLE changes the definition of an existing foreign
47       table. There are several subforms:
48
49       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
56       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
62       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
67       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
72       SET/DROP NOT NULL
73           Mark a column as allowing, or not allowing, null values.
74
75       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
80       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
85       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
91       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
96           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
104       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
109       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
114       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
119       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
125           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
129       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
134       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
139       NO INHERIT parent_table
140           This form removes the target foreign table from the list of
141           children of the specified parent table.
142
143       OWNER
144           This form changes the owner of the foreign table to the specified
145           user.
146
147       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
155       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
159       SET SCHEMA
160           This form moves the foreign table into another schema.
161
162       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
167       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
171       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

PARAMETERS

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
190       column_name
191           Name of a new or existing column.
192
193       new_column_name
194           New name for an existing column.
195
196       new_name
197           New name for the table.
198
199       data_type
200           Data type of the new column, or new data type for an existing
201           column.
202
203       table_constraint
204           New table constraint for the foreign table.
205
206       constraint_name
207           Name of an existing constraint to drop.
208
209       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
214       RESTRICT
215           Refuse to drop the column or constraint if there are any dependent
216           objects. This is the default behavior.
217
218       trigger_name
219           Name of a single trigger to disable or enable.
220
221       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
227       USER
228           Disable or enable all triggers belonging to the foreign table
229           except for internally generated triggers.
230
231       parent_table
232           A parent table to associate or de-associate with this foreign
233           table.
234
235       new_owner
236           The user name of the new owner of the table.
237
238       new_schema
239           The name of the schema to which the table will be moved.
240

NOTES

242       The key word COLUMN is noise and can be omitted.
243
244       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
250       Refer to CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)) for a further
251       description of valid parameters.
252

EXAMPLES

254       To mark a column as not-null:
255
256           ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
257
258       To change options of a foreign table:
259
260           ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');
261

COMPATIBILITY

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
268       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

SEE ALSO

273       CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)), DROP FOREIGN TABLE
274       (DROP_FOREIGN_TABLE(7))
275
276
277
278PostgreSQL 10.7                      2019               ALTER FOREIGN TABLE(7)
Impressum