| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/create_foreign_table.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-createforeigntable"> |
| 7 |
<indexterm zone="sql-createforeigntable"> |
| 8 |
<primary>CREATE FOREIGN TABLE</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>CREATE FOREIGN TABLE</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>CREATE FOREIGN TABLE</refname> |
| 19 |
<refpurpose>define a new foreign table</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [ |
| 25 |
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ] |
| 26 |
| <replaceable>table_constraint</replaceable> |
| 27 |
| LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] } |
| 28 |
[, ... ] |
| 29 |
] ) |
| 30 |
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ] |
| 31 |
SERVER <replaceable class="parameter">server_name</replaceable> |
| 32 |
[ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] |
| 33 |
|
| 34 |
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> |
| 35 |
PARTITION OF <replaceable class="parameter">parent_table</replaceable> [ ( |
| 36 |
{ <replaceable class="parameter">column_name</replaceable> [ WITH OPTIONS ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ] |
| 37 |
| <replaceable>table_constraint</replaceable> } |
| 38 |
[, ... ] |
| 39 |
) ] |
| 40 |
{ FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } |
| 41 |
SERVER <replaceable class="parameter">server_name</replaceable> |
| 42 |
[ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] |
| 43 |
|
| 44 |
<phrase>where <replaceable class="parameter">column_constraint</replaceable> is:</phrase> |
| 45 |
|
| 46 |
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] |
| 47 |
{ NOT NULL [ NO INHERIT ] | |
| 48 |
NULL | |
| 49 |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | |
| 50 |
DEFAULT <replaceable>default_expr</replaceable> | |
| 51 |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] } |
| 52 |
[ ENFORCED | NOT ENFORCED ] |
| 53 |
|
| 54 |
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase> |
| 55 |
|
| 56 |
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] |
| 57 |
{ NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] | |
| 58 |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] } |
| 59 |
[ ENFORCED | NOT ENFORCED ] |
| 60 |
|
| 61 |
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase> |
| 62 |
|
| 63 |
{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL } |
| 64 |
|
| 65 |
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase> |
| 66 |
|
| 67 |
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) | |
| 68 |
FROM ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) |
| 69 |
TO ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) | |
| 70 |
WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REMAINDER <replaceable class="parameter">numeric_literal</replaceable> ) |
| 71 |
</synopsis> |
| 72 |
</refsynopsisdiv> |
| 73 |
|
| 74 |
<refsect1 id="sql-createforeigntable-description"> |
| 75 |
<title>Description</title> |
| 76 |
|
| 77 |
<para> |
| 78 |
<command>CREATE FOREIGN TABLE</command> creates a new foreign table |
| 79 |
in the current database. The table will be owned by the user issuing the |
| 80 |
command. |
| 81 |
</para> |
| 82 |
|
| 83 |
<para> |
| 84 |
If a schema name is given (for example, <literal>CREATE FOREIGN TABLE |
| 85 |
myschema.mytable ...</literal>) then the table is created in the specified |
| 86 |
schema. Otherwise it is created in the current schema. |
| 87 |
The name of the foreign table must be |
| 88 |
distinct from the name of any other relation (table, sequence, index, view, |
| 89 |
materialized view, or foreign table) in the same schema. |
| 90 |
</para> |
| 91 |
|
| 92 |
<para> |
| 93 |
<command>CREATE FOREIGN TABLE</command> also automatically creates a data |
| 94 |
type that represents the composite type corresponding to one row of |
| 95 |
the foreign table. Therefore, foreign tables cannot have the same |
| 96 |
name as any existing data type in the same schema. |
| 97 |
</para> |
| 98 |
|
| 99 |
<para> |
| 100 |
If <literal>PARTITION OF</literal> clause is specified then the table is |
| 101 |
created as a partition of <literal>parent_table</literal> with specified |
| 102 |
bounds. |
| 103 |
</para> |
| 104 |
|
| 105 |
<para> |
| 106 |
To be able to create a foreign table, you must have <literal>USAGE</literal> |
| 107 |
privilege on the foreign server, as well as <literal>USAGE</literal> |
| 108 |
privilege on all column types used in the table. |
| 109 |
</para> |
| 110 |
</refsect1> |
| 111 |
|
| 112 |
<refsect1> |
| 113 |
<title>Parameters</title> |
| 114 |
|
| 115 |
<variablelist> |
| 116 |
|
| 117 |
<varlistentry> |
| 118 |
<term><literal>IF NOT EXISTS</literal></term> |
| 119 |
<listitem> |
| 120 |
<para> |
| 121 |
Do not throw an error if a relation with the same name already exists. |
| 122 |
A notice is issued in this case. Note that there is no guarantee that |
| 123 |
the existing relation is anything like the one that would have been |
| 124 |
created. |
| 125 |
</para> |
| 126 |
</listitem> |
| 127 |
</varlistentry> |
| 128 |
|
| 129 |
<varlistentry> |
| 130 |
<term><replaceable class="parameter">table_name</replaceable></term> |
| 131 |
<listitem> |
| 132 |
<para> |
| 133 |
The name (optionally schema-qualified) of the table to be created. |
| 134 |
</para> |
| 135 |
</listitem> |
| 136 |
</varlistentry> |
| 137 |
|
| 138 |
<varlistentry> |
| 139 |
<term><replaceable class="parameter">column_name</replaceable></term> |
| 140 |
<listitem> |
| 141 |
<para> |
| 142 |
The name of a column to be created in the new table. |
| 143 |
</para> |
| 144 |
</listitem> |
| 145 |
</varlistentry> |
| 146 |
|
| 147 |
<varlistentry> |
| 148 |
<term><replaceable class="parameter">data_type</replaceable></term> |
| 149 |
<listitem> |
| 150 |
<para> |
| 151 |
The data type of the column. This can include array |
| 152 |
specifiers. For more information on the data types supported by |
| 153 |
<productname>PostgreSQL</productname>, refer to <xref |
| 154 |
linkend="datatype"/>. |
| 155 |
</para> |
| 156 |
</listitem> |
| 157 |
</varlistentry> |
| 158 |
|
| 159 |
<varlistentry> |
| 160 |
<term><literal>COLLATE <replaceable>collation</replaceable></literal></term> |
| 161 |
<listitem> |
| 162 |
<para> |
| 163 |
The <literal>COLLATE</literal> clause assigns a collation to |
| 164 |
the column (which must be of a collatable data type). |
| 165 |
If not specified, the column data type's default collation is used. |
| 166 |
</para> |
| 167 |
</listitem> |
| 168 |
</varlistentry> |
| 169 |
|
| 170 |
<varlistentry> |
| 171 |
<term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term> |
| 172 |
<listitem> |
| 173 |
<para> |
| 174 |
The optional <literal>INHERITS</literal> clause specifies a list of |
| 175 |
tables from which the new foreign table automatically inherits |
| 176 |
all columns. Parent tables can be plain tables or foreign tables. |
| 177 |
See the similar form of |
| 178 |
<link linkend="sql-createtable"><command>CREATE TABLE</command></link> for more details. |
| 179 |
</para> |
| 180 |
</listitem> |
| 181 |
</varlistentry> |
| 182 |
|
| 183 |
<varlistentry> |
| 184 |
<term><literal>PARTITION OF <replaceable>parent_table</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }</literal></term> |
| 185 |
<listitem> |
| 186 |
<para> |
| 187 |
This form can be used to create the foreign table as partition of |
| 188 |
the given parent table with specified partition bound values. |
| 189 |
See the similar form of |
| 190 |
<link linkend="sql-createtable"><command>CREATE TABLE</command></link> for more details. |
| 191 |
Note that it is currently not allowed to create the foreign table as a |
| 192 |
partition of the parent table if there are <literal>UNIQUE</literal> |
| 193 |
indexes on the parent table. (See also |
| 194 |
<link linkend="sql-altertable"><command>ALTER TABLE ATTACH PARTITION</command></link>.) |
| 195 |
</para> |
| 196 |
</listitem> |
| 197 |
</varlistentry> |
| 198 |
|
| 199 |
<varlistentry> |
| 200 |
<term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term> |
| 201 |
<listitem> |
| 202 |
<para> |
| 203 |
The <literal>LIKE</literal> clause specifies a table from which |
| 204 |
the new table automatically copies all column names, their data types, |
| 205 |
and their not-null constraints. |
| 206 |
</para> |
| 207 |
<para> |
| 208 |
Unlike <literal>INHERITS</literal>, the new table and original table |
| 209 |
are completely decoupled after creation is complete. Changes to the |
| 210 |
original table will not be applied to the new table, and it is not |
| 211 |
possible to include data of the new table in scans of the original |
| 212 |
table. |
| 213 |
</para> |
| 214 |
<para> |
| 215 |
Also unlike <literal>INHERITS</literal>, columns and |
| 216 |
constraints copied by <literal>LIKE</literal> are not merged with similarly |
| 217 |
named columns and constraints. |
| 218 |
If the same name is specified explicitly or in another |
| 219 |
<literal>LIKE</literal> clause, an error is signaled. |
| 220 |
</para> |
| 221 |
<para> |
| 222 |
The optional <replaceable>like_option</replaceable> clauses specify |
| 223 |
which additional properties of the original table to copy. Specifying |
| 224 |
<literal>INCLUDING</literal> copies the property, specifying |
| 225 |
<literal>EXCLUDING</literal> omits the property. |
| 226 |
<literal>EXCLUDING</literal> is the default. If multiple specifications |
| 227 |
are made for the same kind of object, the last one is used. The |
| 228 |
available options are: |
| 229 |
|
| 230 |
<variablelist> |
| 231 |
<varlistentry> |
| 232 |
<term><literal>INCLUDING COMMENTS</literal></term> |
| 233 |
<listitem> |
| 234 |
<para> |
| 235 |
Comments for the copied columns, constraints, and extended statistics |
| 236 |
will be copied. The default behavior is to exclude comments, |
| 237 |
resulting in the corresponding objects in the new table having no |
| 238 |
comments. |
| 239 |
</para> |
| 240 |
</listitem> |
| 241 |
</varlistentry> |
| 242 |
|
| 243 |
<varlistentry> |
| 244 |
<term><literal>INCLUDING CONSTRAINTS</literal></term> |
| 245 |
<listitem> |
| 246 |
<para> |
| 247 |
<literal>CHECK</literal> constraints will be copied. No distinction |
| 248 |
is made between column constraints and table constraints. Not-null |
| 249 |
constraints are always copied to the new table. |
| 250 |
</para> |
| 251 |
</listitem> |
| 252 |
</varlistentry> |
| 253 |
|
| 254 |
<varlistentry> |
| 255 |
<term><literal>INCLUDING DEFAULTS</literal></term> |
| 256 |
<listitem> |
| 257 |
<para> |
| 258 |
Default expressions for the copied column definitions will be |
| 259 |
copied. Otherwise, default expressions are not copied, resulting in |
| 260 |
the copied columns in the new table having null defaults. Note that |
| 261 |
copying defaults that call database-modification functions, such as |
| 262 |
<function>nextval</function>, may create a functional linkage |
| 263 |
between the original and new tables. |
| 264 |
</para> |
| 265 |
</listitem> |
| 266 |
</varlistentry> |
| 267 |
|
| 268 |
<varlistentry> |
| 269 |
<term><literal>INCLUDING GENERATED</literal></term> |
| 270 |
<listitem> |
| 271 |
<para> |
| 272 |
Any generation expressions of copied column definitions will be |
| 273 |
copied. By default, new columns will be regular base columns. |
| 274 |
</para> |
| 275 |
</listitem> |
| 276 |
</varlistentry> |
| 277 |
|
| 278 |
<varlistentry> |
| 279 |
<term><literal>INCLUDING STATISTICS</literal></term> |
| 280 |
<listitem> |
| 281 |
<para> |
| 282 |
Extended statistics are copied to the new table. |
| 283 |
</para> |
| 284 |
</listitem> |
| 285 |
</varlistentry> |
| 286 |
|
| 287 |
<varlistentry> |
| 288 |
<term><literal>INCLUDING ALL</literal></term> |
| 289 |
<listitem> |
| 290 |
<para> |
| 291 |
<literal>INCLUDING ALL</literal> is an abbreviated form selecting |
| 292 |
all the available individual options. (It could be useful to write |
| 293 |
individual <literal>EXCLUDING</literal> clauses after |
| 294 |
<literal>INCLUDING ALL</literal> to select all but some specific |
| 295 |
options.) |
| 296 |
</para> |
| 297 |
</listitem> |
| 298 |
</varlistentry> |
| 299 |
</variablelist> |
| 300 |
</para> |
| 301 |
</listitem> |
| 302 |
</varlistentry> |
| 303 |
|
| 304 |
<varlistentry> |
| 305 |
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term> |
| 306 |
<listitem> |
| 307 |
<para> |
| 308 |
An optional name for a column or table constraint. If the |
| 309 |
constraint is violated, the constraint name is present in error messages, |
| 310 |
so constraint names like <literal>col must be positive</literal> can be used |
| 311 |
to communicate helpful constraint information to client applications. |
| 312 |
(Double-quotes are needed to specify constraint names that contain spaces.) |
| 313 |
If a constraint name is not specified, the system generates a name. |
| 314 |
</para> |
| 315 |
</listitem> |
| 316 |
</varlistentry> |
| 317 |
|
| 318 |
<varlistentry> |
| 319 |
<term><literal>NOT NULL</literal> [ NO INHERIT ]</term> |
| 320 |
<listitem> |
| 321 |
<para> |
| 322 |
The column is not allowed to contain null values. |
| 323 |
</para> |
| 324 |
|
| 325 |
<para> |
| 326 |
A constraint marked with <literal>NO INHERIT</literal> will not propagate to |
| 327 |
child tables. |
| 328 |
</para> |
| 329 |
</listitem> |
| 330 |
</varlistentry> |
| 331 |
|
| 332 |
<varlistentry> |
| 333 |
<term><literal>NULL</literal></term> |
| 334 |
<listitem> |
| 335 |
<para> |
| 336 |
The column is allowed to contain null values. This is the default. |
| 337 |
</para> |
| 338 |
|
| 339 |
<para> |
| 340 |
This clause is only provided for compatibility with |
| 341 |
non-standard SQL databases. Its use is discouraged in new |
| 342 |
applications. |
| 343 |
</para> |
| 344 |
</listitem> |
| 345 |
</varlistentry> |
| 346 |
|
| 347 |
<varlistentry> |
| 348 |
<term><literal>CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] </literal></term> |
| 349 |
<listitem> |
| 350 |
<para> |
| 351 |
The <literal>CHECK</literal> clause specifies an expression producing a |
| 352 |
Boolean result which each row in the foreign table is expected |
| 353 |
to satisfy; that is, the expression should produce TRUE or UNKNOWN, |
| 354 |
never FALSE, for all rows in the foreign table. |
| 355 |
A check constraint specified as a column constraint should |
| 356 |
reference that column's value only, while an expression |
| 357 |
appearing in a table constraint can reference multiple columns. |
| 358 |
</para> |
| 359 |
|
| 360 |
<para> |
| 361 |
Currently, <literal>CHECK</literal> expressions cannot contain |
| 362 |
subqueries nor refer to variables other than columns of the |
| 363 |
current row. The system column <literal>tableoid</literal> |
| 364 |
may be referenced, but not any other system column. |
| 365 |
</para> |
| 366 |
|
| 367 |
<para> |
| 368 |
A constraint marked with <literal>NO INHERIT</literal> will not propagate to |
| 369 |
child tables. |
| 370 |
</para> |
| 371 |
</listitem> |
| 372 |
</varlistentry> |
| 373 |
|
| 374 |
<varlistentry> |
| 375 |
<term><literal>DEFAULT |
| 376 |
<replaceable>default_expr</replaceable></literal></term> |
| 377 |
<listitem> |
| 378 |
<para> |
| 379 |
The <literal>DEFAULT</literal> clause assigns a default data value for |
| 380 |
the column whose column definition it appears within. The value |
| 381 |
is any variable-free expression (subqueries and cross-references |
| 382 |
to other columns in the current table are not allowed). The |
| 383 |
data type of the default expression must match the data type of the |
| 384 |
column. |
| 385 |
</para> |
| 386 |
|
| 387 |
<para> |
| 388 |
The default expression will be used in any insert operation that |
| 389 |
does not specify a value for the column. If there is no default |
| 390 |
for a column, then the default is null. |
| 391 |
</para> |
| 392 |
</listitem> |
| 393 |
</varlistentry> |
| 394 |
|
| 395 |
<varlistentry> |
| 396 |
<term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term> |
| 397 |
<listitem> |
| 398 |
<para> |
| 399 |
This clause creates the column as a <firstterm>generated |
| 400 |
column</firstterm>. The column cannot be written to, and when read the |
| 401 |
result of the specified expression will be returned. |
| 402 |
</para> |
| 403 |
|
| 404 |
<para> |
| 405 |
When <literal>VIRTUAL</literal> is specified, the column will be |
| 406 |
computed when it is read. (The foreign-data wrapper will see it as a |
| 407 |
null value in new rows and may choose to store it as a null value or |
| 408 |
ignore it altogether.) When <literal>STORED</literal> is specified, the |
| 409 |
column will be computed on write. (The computed value will be presented |
| 410 |
to the foreign-data wrapper for storage and must be returned on |
| 411 |
reading.) <literal>VIRTUAL</literal> is the default. |
| 412 |
</para> |
| 413 |
|
| 414 |
<para> |
| 415 |
The generation expression can refer to other columns in the table, but |
| 416 |
not other generated columns. Any functions and operators used must be |
| 417 |
immutable. References to other tables are not allowed. |
| 418 |
</para> |
| 419 |
</listitem> |
| 420 |
</varlistentry> |
| 421 |
|
| 422 |
<varlistentry> |
| 423 |
<term><replaceable class="parameter">server_name</replaceable></term> |
| 424 |
<listitem> |
| 425 |
<para> |
| 426 |
The name of an existing foreign server to use for the foreign table. |
| 427 |
For details on defining a server, see <xref |
| 428 |
linkend="sql-createserver"/>. |
| 429 |
</para> |
| 430 |
</listitem> |
| 431 |
</varlistentry> |
| 432 |
|
| 433 |
<varlistentry> |
| 434 |
<term><literal>OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ...] )</literal></term> |
| 435 |
<listitem> |
| 436 |
<para> |
| 437 |
Options to be associated with the new foreign table or one of its |
| 438 |
columns. |
| 439 |
The allowed option names and values are specific to each foreign |
| 440 |
data wrapper and are validated using the foreign-data wrapper's |
| 441 |
validator function. Duplicate option names are not allowed (although |
| 442 |
it's OK for a table option and a column option to have the same name). |
| 443 |
</para> |
| 444 |
</listitem> |
| 445 |
</varlistentry> |
| 446 |
|
| 447 |
</variablelist> |
| 448 |
|
| 449 |
</refsect1> |
| 450 |
|
| 451 |
<refsect1> |
| 452 |
<title>Notes</title> |
| 453 |
|
| 454 |
<para> |
| 455 |
Constraints on foreign tables (such as <literal>CHECK</literal> |
| 456 |
or <literal>NOT NULL</literal> clauses) are not enforced by the |
| 457 |
core <productname>PostgreSQL</productname> system, and most foreign data wrappers |
| 458 |
do not attempt to enforce them either; that is, the constraint is |
| 459 |
simply assumed to hold true. There would be little point in such |
| 460 |
enforcement since it would only apply to rows inserted or updated via |
| 461 |
the foreign table, and not to rows modified by other means, such as |
| 462 |
directly on the remote server. Instead, a constraint attached to a |
| 463 |
foreign table should represent a constraint that is being enforced by |
| 464 |
the remote server. |
| 465 |
</para> |
| 466 |
|
| 467 |
<para> |
| 468 |
Some special-purpose foreign data wrappers might be the only access |
| 469 |
mechanism for the data they access, and in that case it might be |
| 470 |
appropriate for the foreign data wrapper itself to perform constraint |
| 471 |
enforcement. But you should not assume that a wrapper does that |
| 472 |
unless its documentation says so. |
| 473 |
</para> |
| 474 |
|
| 475 |
<para> |
| 476 |
Although <productname>PostgreSQL</productname> does not attempt to enforce |
| 477 |
constraints on foreign tables, it does assume that they are correct |
| 478 |
for purposes of query optimization. If there are rows visible in the |
| 479 |
foreign table that do not satisfy a declared constraint, queries on |
| 480 |
the table might produce errors or incorrect answers. It is the user's |
| 481 |
responsibility to ensure that the constraint definition matches |
| 482 |
reality. |
| 483 |
</para> |
| 484 |
|
| 485 |
<caution> |
| 486 |
<para> |
| 487 |
When a foreign table is used as a partition of a partitioned table, |
| 488 |
there is an implicit constraint that its contents must satisfy the |
| 489 |
partitioning rule. Again, it is the user's responsibility to ensure |
| 490 |
that that is true, which is best done by installing a matching |
| 491 |
constraint on the remote server. |
| 492 |
</para> |
| 493 |
</caution> |
| 494 |
|
| 495 |
<para> |
| 496 |
Within a partitioned table containing foreign-table partitions, |
| 497 |
an <command>UPDATE</command> that changes the partition key value can |
| 498 |
cause a row to be moved from a local partition to a foreign-table |
| 499 |
partition, provided the foreign data wrapper supports tuple routing. |
| 500 |
However, it is not currently possible to move a row from a |
| 501 |
foreign-table partition to another partition. |
| 502 |
An <command>UPDATE</command> that would require doing that will fail |
| 503 |
due to the partitioning constraint, assuming that that is properly |
| 504 |
enforced by the remote server. |
| 505 |
</para> |
| 506 |
|
| 507 |
<para> |
| 508 |
Similar considerations apply to generated columns. Stored generated |
| 509 |
columns are computed on insert or update on the local |
| 510 |
<productname>PostgreSQL</productname> server and handed to the |
| 511 |
foreign-data wrapper for writing out to the foreign data store, but it is |
| 512 |
not enforced that a query of the foreign table returns values for stored |
| 513 |
generated columns that are consistent with the generation expression. |
| 514 |
Again, this might result in incorrect query results. |
| 515 |
</para> |
| 516 |
</refsect1> |
| 517 |
|
| 518 |
<refsect1 id="sql-createforeigntable-examples"> |
| 519 |
<title>Examples</title> |
| 520 |
|
| 521 |
<para> |
| 522 |
Create foreign table <structname>films</structname>, which will be accessed through |
| 523 |
the server <structname>film_server</structname>: |
| 524 |
|
| 525 |
<programlisting> |
| 526 |
CREATE FOREIGN TABLE films ( |
| 527 |
code char(5) NOT NULL, |
| 528 |
title varchar(40) NOT NULL, |
| 529 |
did integer NOT NULL, |
| 530 |
date_prod date, |
| 531 |
kind varchar(10), |
| 532 |
len interval hour to minute |
| 533 |
) |
| 534 |
SERVER film_server; |
| 535 |
</programlisting></para> |
| 536 |
|
| 537 |
<para> |
| 538 |
Create foreign table <structname>measurement_y2016m07</structname>, which will be |
| 539 |
accessed through the server <structname>server_07</structname>, as a partition |
| 540 |
of the range partitioned table <structname>measurement</structname>: |
| 541 |
|
| 542 |
<programlisting> |
| 543 |
CREATE FOREIGN TABLE measurement_y2016m07 |
| 544 |
PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01') |
| 545 |
SERVER server_07; |
| 546 |
</programlisting></para> |
| 547 |
|
| 548 |
</refsect1> |
| 549 |
|
| 550 |
<refsect1 id="sql-createforeigntable-compatibility"> |
| 551 |
<title>Compatibility</title> |
| 552 |
|
| 553 |
<para> |
| 554 |
The <command>CREATE FOREIGN TABLE</command> command largely conforms to the |
| 555 |
<acronym>SQL</acronym> standard; however, much as with |
| 556 |
<link linkend="sql-createtable"><command>CREATE TABLE</command></link>, |
| 557 |
<literal>NULL</literal> constraints and zero-column foreign tables are permitted. |
| 558 |
The ability to specify column default values is also |
| 559 |
a <productname>PostgreSQL</productname> extension. Table inheritance, in the form |
| 560 |
defined by <productname>PostgreSQL</productname>, is nonstandard. |
| 561 |
The <literal>LIKE</literal> clause, as supported in this command, is |
| 562 |
nonstandard. |
| 563 |
</para> |
| 564 |
|
| 565 |
</refsect1> |
| 566 |
|
| 567 |
<refsect1> |
| 568 |
<title>See Also</title> |
| 569 |
|
| 570 |
<simplelist type="inline"> |
| 571 |
<member><xref linkend="sql-alterforeigntable"/></member> |
| 572 |
<member><xref linkend="sql-dropforeigntable"/></member> |
| 573 |
<member><xref linkend="sql-createtable"/></member> |
| 574 |
<member><xref linkend="sql-createserver"/></member> |
| 575 |
<member><xref linkend="sql-importforeignschema"/></member> |
| 576 |
</simplelist> |
| 577 |
</refsect1> |
| 578 |
</refentry> |