1ALTER FOREIGN TABLE(7)   PostgreSQL 12.6 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 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

DESCRIPTION

45       ALTER FOREIGN TABLE changes the definition of an existing foreign
46       table. There are several subforms:
47
48       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
55       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
61       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
66       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
71       SET/DROP NOT NULL
72           Mark a column as allowing, or not allowing, null values.
73
74       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
79       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
84       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
90       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
95           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
103       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
108       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
113       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
118       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
123       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
128       NO INHERIT parent_table
129           This form removes the target foreign table from the list of
130           children of the specified parent table.
131
132       OWNER
133           This form changes the owner of the foreign table to the specified
134           user.
135
136       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
144       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
148       SET SCHEMA
149           This form moves the foreign table into another schema.
150
151       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
156       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
160       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

PARAMETERS

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
179       column_name
180           Name of a new or existing column.
181
182       new_column_name
183           New name for an existing column.
184
185       new_name
186           New name for the table.
187
188       data_type
189           Data type of the new column, or new data type for an existing
190           column.
191
192       table_constraint
193           New table constraint for the foreign table.
194
195       constraint_name
196           Name of an existing constraint to drop.
197
198       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
203       RESTRICT
204           Refuse to drop the column or constraint if there are any dependent
205           objects. This is the default behavior.
206
207       trigger_name
208           Name of a single trigger to disable or enable.
209
210       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
216       USER
217           Disable or enable all triggers belonging to the foreign table
218           except for internally generated triggers.
219
220       parent_table
221           A parent table to associate or de-associate with this foreign
222           table.
223
224       new_owner
225           The user name of the new owner of the table.
226
227       new_schema
228           The name of the schema to which the table will be moved.
229

NOTES

231       The key word COLUMN is noise and can be omitted.
232
233       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
239       Refer to CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)) for a further
240       description of valid parameters.
241

EXAMPLES

243       To mark a column as not-null:
244
245           ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
246
247       To change options of a foreign table:
248
249           ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');
250

COMPATIBILITY

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

SEE ALSO

262       CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)), DROP FOREIGN TABLE
263       (DROP_FOREIGN_TABLE(7))
264
265
266
267PostgreSQL 12.6                      2021               ALTER FOREIGN TABLE(7)
Impressum