1CREATE FOREIGN TABLE(7) PostgreSQL 16.1 Documentation CREATE FOREIGN TABLE(7)
2
6 CREATE_FOREIGN_TABLE - define a new foreign table
7
9 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
10 { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
11 | table_constraint }
12 [, ... ]
13 ] )
14 [ INHERITS ( parent_table [, ... ] ) ]
15 SERVER server_name
16 [ OPTIONS ( option 'value' [, ... ] ) ]
17 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
19 PARTITION OF parent_table [ (
20 { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
21 | table_constraint }
22 [, ... ]
23 ) ]
24 { FOR VALUES partition_bound_spec | DEFAULT }
25 SERVER server_name
26 [ OPTIONS ( option 'value' [, ... ] ) ]
27 where column_constraint is:
29 [ CONSTRAINT constraint_name ]
31 { NOT NULL |
32 NULL |
33 CHECK ( expression ) [ NO INHERIT ] |
34 DEFAULT default_expr |
35 GENERATED ALWAYS AS ( generation_expr ) STORED }
36 and table_constraint is:
38 [ CONSTRAINT constraint_name ]
40 CHECK ( expression ) [ NO INHERIT ]
41 and partition_bound_spec is:
43 IN ( partition_bound_expr [, ...] ) |
45 FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
46 TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
47 WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
48
50 CREATE FOREIGN TABLE creates a new foreign table in the current
51 database. The table will be owned by the user issuing the command.
52 If a schema name is given (for example, CREATE FOREIGN TABLE
54 myschema.mytable ...) then the table is created in the specified
55 schema. Otherwise it is created in the current schema. The name of the
56 foreign table must be distinct from the name of any other relation
57 (table, sequence, index, view, materialized view, or foreign table) in
58 the same schema.
59 CREATE FOREIGN TABLE also automatically creates a data type that
61 represents the composite type corresponding to one row of the foreign
62 table. Therefore, foreign tables cannot have the same name as any
63 existing data type in the same schema.
64 If PARTITION OF clause is specified then the table is created as a
66 partition of parent_table with specified bounds.
67 To be able to create a foreign table, you must have USAGE privilege on
69 the foreign server, as well as USAGE privilege on all column types used
70 in the table.
71
73 IF NOT EXISTS
74 Do not throw an error if a relation with the same name already
75 exists. A notice is issued in this case. Note that there is no
76 guarantee that the existing relation is anything like the one that
77 would have been created.
78 table_name
80 The name (optionally schema-qualified) of the table to be created.
81 column_name
83 The name of a column to be created in the new table.
84 data_type
86 The data type of the column. This can include array specifiers. For
87 more information on the data types supported by PostgreSQL, refer
88 to Chapter 8.
89 COLLATE collation
91 The COLLATE clause assigns a collation to the column (which must be
92 of a collatable data type). If not specified, the column data
93 type's default collation is used.
94 INHERITS ( parent_table [, ... ] )
96 The optional INHERITS clause specifies a list of tables from which
97 the new foreign table automatically inherits all columns. Parent
98 tables can be plain tables or foreign tables. See the similar form
99 of CREATE TABLE for more details.
100 PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
102 This form can be used to create the foreign table as partition of
103 the given parent table with specified partition bound values. See
104 the similar form of CREATE TABLE for more details. Note that it is
105 currently not allowed to create the foreign table as a partition of
106 the parent table if there are UNIQUE indexes on the parent table.
107 (See also ALTER TABLE ATTACH PARTITION.)
108 CONSTRAINT constraint_name
110 An optional name for a column or table constraint. If the
111 constraint is violated, the constraint name is present in error
112 messages, so constraint names like col must be positive can be used
113 to communicate helpful constraint information to client
114 applications. (Double-quotes are needed to specify constraint names
115 that contain spaces.) If a constraint name is not specified, the
116 system generates a name.
117 NOT NULL
119 The column is not allowed to contain null values.
120 NULL
122 The column is allowed to contain null values. This is the default.
123 This clause is only provided for compatibility with non-standard
125 SQL databases. Its use is discouraged in new applications.
126 CHECK ( expression ) [ NO INHERIT ]
128 The CHECK clause specifies an expression producing a Boolean result
129 which each row in the foreign table is expected to satisfy; that
130 is, the expression should produce TRUE or UNKNOWN, never FALSE, for
131 all rows in the foreign table. A check constraint specified as a
132 column constraint should reference that column's value only, while
133 an expression appearing in a table constraint can reference
134 multiple columns.
135 Currently, CHECK expressions cannot contain subqueries nor refer to
137 variables other than columns of the current row. The system column
138 tableoid may be referenced, but not any other system column.
139 A constraint marked with NO INHERIT will not propagate to child
141 tables.
142 DEFAULT default_expr
144 The DEFAULT clause assigns a default data value for the column
145 whose column definition it appears within. The value is any
146 variable-free expression (subqueries and cross-references to other
147 columns in the current table are not allowed). The data type of the
148 default expression must match the data type of the column.
149 The default expression will be used in any insert operation that
151 does not specify a value for the column. If there is no default for
152 a column, then the default is null.
153 GENERATED ALWAYS AS ( generation_expr ) STORED
155 This clause creates the column as a generated column. The column
156 cannot be written to, and when read the result of the specified
157 expression will be returned.
158 The keyword STORED is required to signify that the column will be
160 computed on write. (The computed value will be presented to the
161 foreign-data wrapper for storage and must be returned on reading.)
162 The generation expression can refer to other columns in the table,
164 but not other generated columns. Any functions and operators used
165 must be immutable. References to other tables are not allowed.
166 server_name
168 The name of an existing foreign server to use for the foreign
169 table. For details on defining a server, see CREATE SERVER
170 (CREATE_SERVER(7)).
171 OPTIONS ( option 'value' [, ...] )
173 Options to be associated with the new foreign table or one of its
174 columns. The allowed option names and values are specific to each
175 foreign data wrapper and are validated using the foreign-data
176 wrapper's validator function. Duplicate option names are not
177 allowed (although it's OK for a table option and a column option to
178 have the same name).
179
181 Constraints on foreign tables (such as CHECK or NOT NULL clauses) are
182 not enforced by the core PostgreSQL system, and most foreign data
183 wrappers do not attempt to enforce them either; that is, the constraint
184 is simply assumed to hold true. There would be little point in such
185 enforcement since it would only apply to rows inserted or updated via
186 the foreign table, and not to rows modified by other means, such as
187 directly on the remote server. Instead, a constraint attached to a
188 foreign table should represent a constraint that is being enforced by
189 the remote server.
190 Some special-purpose foreign data wrappers might be the only access
192 mechanism for the data they access, and in that case it might be
193 appropriate for the foreign data wrapper itself to perform constraint
194 enforcement. But you should not assume that a wrapper does that unless
195 its documentation says so.
196 Although PostgreSQL does not attempt to enforce constraints on foreign
198 tables, it does assume that they are correct for purposes of query
199 optimization. If there are rows visible in the foreign table that do
200 not satisfy a declared constraint, queries on the table might produce
201 errors or incorrect answers. It is the user's responsibility to ensure
202 that the constraint definition matches reality.
203 Caution
205 When a foreign table is used as a partition of a partitioned table,
206 there is an implicit constraint that its contents must satisfy the
207 partitioning rule. Again, it is the user's responsibility to ensure
208 that that is true, which is best done by installing a matching
209 constraint on the remote server.
210 Within a partitioned table containing foreign-table partitions, an
212 UPDATE that changes the partition key value can cause a row to be moved
213 from a local partition to a foreign-table partition, provided the
214 foreign data wrapper supports tuple routing. However, it is not
215 currently possible to move a row from a foreign-table partition to
216 another partition. An UPDATE that would require doing that will fail
217 due to the partitioning constraint, assuming that that is properly
218 enforced by the remote server.
219 Similar considerations apply to generated columns. Stored generated
221 columns are computed on insert or update on the local PostgreSQL server
222 and handed to the foreign-data wrapper for writing out to the foreign
223 data store, but it is not enforced that a query of the foreign table
224 returns values for stored generated columns that are consistent with
225 the generation expression. Again, this might result in incorrect query
226 results.
227
229 Create foreign table films, which will be accessed through the server
230 film_server:
231 CREATE FOREIGN TABLE films (
233 code char(5) NOT NULL,
234 title varchar(40) NOT NULL,
235 did integer NOT NULL,
236 date_prod date,
237 kind varchar(10),
238 len interval hour to minute
239 )
240 SERVER film_server;
241 Create foreign table measurement_y2016m07, which will be accessed
243 through the server server_07, as a partition of the range partitioned
244 table measurement:
245 CREATE FOREIGN TABLE measurement_y2016m07
247 PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
248 SERVER server_07;
249
251 The CREATE FOREIGN TABLE command largely conforms to the SQL standard;
252 however, much as with CREATE TABLE, NULL constraints and zero-column
253 foreign tables are permitted. The ability to specify column default
254 values is also a PostgreSQL extension. Table inheritance, in the form
255 defined by PostgreSQL, is nonstandard.
256
258 ALTER FOREIGN TABLE (ALTER_FOREIGN_TABLE(7)), DROP FOREIGN TABLE
259 (DROP_FOREIGN_TABLE(7)), CREATE TABLE (CREATE_TABLE(7)), CREATE SERVER
260 (CREATE_SERVER(7)), IMPORT FOREIGN SCHEMA (IMPORT_FOREIGN_SCHEMA(7))
261PostgreSQL 16.1 2023 CREATE FOREIGN TABLE(7)