begriffs open source - ai-pg/blob - full-docs/txt/sql-createforeigntable.txt

   1
   2 CREATE FOREIGN TABLE
   3
   4    CREATE FOREIGN TABLE — define a new foreign table
   5
   6 Synopsis
   7
   8 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
   9   { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE coll
  10 ation ] [ column_constraint [ ... ] ]
  11     | table_constraint
  12     | LIKE source_table [ like_option ... ] }
  13     [, ... ]
  14 ] )
  15 [ INHERITS ( parent_table [, ... ] ) ]
  16   SERVER server_name
  17 [ OPTIONS ( option 'value' [, ... ] ) ]
  18
  19 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  20   PARTITION OF parent_table [ (
  21   { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
  22     | table_constraint }
  23     [, ... ]
  24 ) ]
  25 { FOR VALUES partition_bound_spec | DEFAULT }
  26   SERVER server_name
  27 [ OPTIONS ( option 'value' [, ... ] ) ]
  28
  29 where column_constraint is:
  30
  31 [ CONSTRAINT constraint_name ]
  32 { NOT NULL [ NO INHERIT ] |
  33   NULL |
  34   CHECK ( expression ) [ NO INHERIT ] |
  35   DEFAULT default_expr |
  36   GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] }
  37 [ ENFORCED | NOT ENFORCED ]
  38
  39 and table_constraint is:
  40
  41 [ CONSTRAINT constraint_name ]
  42 {  NOT NULL column_name [ NO INHERIT ] |
  43    CHECK ( expression ) [ NO INHERIT ] }
  44 [ ENFORCED | NOT ENFORCED ]
  45
  46 and like_option is:
  47
  48 { INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STAT
  49 ISTICS | ALL }
  50
  51 and partition_bound_spec is:
  52
  53 IN ( partition_bound_expr [, ...] ) |
  54 FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  55   TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
  56 WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
  57
  58 Description
  59
  60    CREATE FOREIGN TABLE creates a new foreign table in the current
  61    database. The table will be owned by the user issuing the command.
  62
  63    If a schema name is given (for example, CREATE FOREIGN TABLE
  64    myschema.mytable ...) then the table is created in the specified
  65    schema. Otherwise it is created in the current schema. The name of the
  66    foreign table must be distinct from the name of any other relation
  67    (table, sequence, index, view, materialized view, or foreign table) in
  68    the same schema.
  69
  70    CREATE FOREIGN TABLE also automatically creates a data type that
  71    represents the composite type corresponding to one row of the foreign
  72    table. Therefore, foreign tables cannot have the same name as any
  73    existing data type in the same schema.
  74
  75    If PARTITION OF clause is specified then the table is created as a
  76    partition of parent_table with specified bounds.
  77
  78    To be able to create a foreign table, you must have USAGE privilege on
  79    the foreign server, as well as USAGE privilege on all column types used
  80    in the table.
  81
  82 Parameters
  83
  84    IF NOT EXISTS
  85           Do not throw an error if a relation with the same name already
  86           exists. A notice is issued in this case. Note that there is no
  87           guarantee that the existing relation is anything like the one
  88           that would have been created.
  89
  90    table_name
  91           The name (optionally schema-qualified) of the table to be
  92           created.
  93
  94    column_name
  95           The name of a column to be created in the new table.
  96
  97    data_type
  98           The data type of the column. This can include array specifiers.
  99           For more information on the data types supported by PostgreSQL,
 100           refer to Chapter 8.
 101
 102    COLLATE collation
 103           The COLLATE clause assigns a collation to the column (which must
 104           be of a collatable data type). If not specified, the column data
 105           type's default collation is used.
 106
 107    INHERITS ( parent_table [, ... ] )
 108           The optional INHERITS clause specifies a list of tables from
 109           which the new foreign table automatically inherits all columns.
 110           Parent tables can be plain tables or foreign tables. See the
 111           similar form of CREATE TABLE for more details.
 112
 113    PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
 114           This form can be used to create the foreign table as partition
 115           of the given parent table with specified partition bound values.
 116           See the similar form of CREATE TABLE for more details. Note that
 117           it is currently not allowed to create the foreign table as a
 118           partition of the parent table if there are UNIQUE indexes on the
 119           parent table. (See also ALTER TABLE ATTACH PARTITION.)
 120
 121    LIKE source_table [ like_option ... ]
 122           The LIKE clause specifies a table from which the new table
 123           automatically copies all column names, their data types, and
 124           their not-null constraints.
 125
 126           Unlike INHERITS, the new table and original table are completely
 127           decoupled after creation is complete. Changes to the original
 128           table will not be applied to the new table, and it is not
 129           possible to include data of the new table in scans of the
 130           original table.
 131
 132           Also unlike INHERITS, columns and constraints copied by LIKE are
 133           not merged with similarly named columns and constraints. If the
 134           same name is specified explicitly or in another LIKE clause, an
 135           error is signaled.
 136
 137           The optional like_option clauses specify which additional
 138           properties of the original table to copy. Specifying INCLUDING
 139           copies the property, specifying EXCLUDING omits the property.
 140           EXCLUDING is the default. If multiple specifications are made
 141           for the same kind of object, the last one is used. The available
 142           options are:
 143
 144         INCLUDING COMMENTS
 145                 Comments for the copied columns and constraints will be
 146                 copied. The default behavior is to exclude comments,
 147                 resulting in the copied columns and constraints in the new
 148                 table having no comments.
 149
 150         INCLUDING CONSTRAINTS
 151                 CHECK constraints will be copied. No distinction is made
 152                 between column constraints and table constraints. Not-null
 153                 constraints are always copied to the new table.
 154
 155         INCLUDING DEFAULTS
 156                 Default expressions for the copied column definitions will
 157                 be copied. Otherwise, default expressions are not copied,
 158                 resulting in the copied columns in the new table having
 159                 null defaults. Note that copying defaults that call
 160                 database-modification functions, such as nextval, may
 161                 create a functional linkage between the original and new
 162                 tables.
 163
 164         INCLUDING GENERATED
 165                 Any generation expressions of copied column definitions
 166                 will be copied. By default, new columns will be regular
 167                 base columns.
 168
 169         INCLUDING STATISTICS
 170                 Extended statistics are copied to the new table.
 171
 172         INCLUDING ALL
 173                 INCLUDING ALL is an abbreviated form selecting all the
 174                 available individual options. (It could be useful to write
 175                 individual EXCLUDING clauses after INCLUDING ALL to select
 176                 all but some specific options.)
 177
 178    CONSTRAINT constraint_name
 179           An optional name for a column or table constraint. If the
 180           constraint is violated, the constraint name is present in error
 181           messages, so constraint names like col must be positive can be
 182           used to communicate helpful constraint information to client
 183           applications. (Double-quotes are needed to specify constraint
 184           names that contain spaces.) If a constraint name is not
 185           specified, the system generates a name.
 186
 187    NOT NULL [ NO INHERIT ]
 188           The column is not allowed to contain null values.
 189
 190           A constraint marked with NO INHERIT will not propagate to child
 191           tables.
 192
 193    NULL
 194           The column is allowed to contain null values. This is the
 195           default.
 196
 197           This clause is only provided for compatibility with non-standard
 198           SQL databases. Its use is discouraged in new applications.
 199
 200    CHECK ( expression ) [ NO INHERIT ]
 201           The CHECK clause specifies an expression producing a Boolean
 202           result which each row in the foreign table is expected to
 203           satisfy; that is, the expression should produce TRUE or UNKNOWN,
 204           never FALSE, for all rows in the foreign table. A check
 205           constraint specified as a column constraint should reference
 206           that column's value only, while an expression appearing in a
 207           table constraint can reference multiple columns.
 208
 209           Currently, CHECK expressions cannot contain subqueries nor refer
 210           to variables other than columns of the current row. The system
 211           column tableoid may be referenced, but not any other system
 212           column.
 213
 214           A constraint marked with NO INHERIT will not propagate to child
 215           tables.
 216
 217    DEFAULT default_expr
 218           The DEFAULT clause assigns a default data value for the column
 219           whose column definition it appears within. The value is any
 220           variable-free expression (subqueries and cross-references to
 221           other columns in the current table are not allowed). The data
 222           type of the default expression must match the data type of the
 223           column.
 224
 225           The default expression will be used in any insert operation that
 226           does not specify a value for the column. If there is no default
 227           for a column, then the default is null.
 228
 229    GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ]
 230           This clause creates the column as a generated column. The column
 231           cannot be written to, and when read the result of the specified
 232           expression will be returned.
 233
 234           When VIRTUAL is specified, the column will be computed when it
 235           is read. (The foreign-data wrapper will see it as a null value
 236           in new rows and may choose to store it as a null value or ignore
 237           it altogether.) When STORED is specified, the column will be
 238           computed on write. (The computed value will be presented to the
 239           foreign-data wrapper for storage and must be returned on
 240           reading.) VIRTUAL is the default.
 241
 242           The generation expression can refer to other columns in the
 243           table, but not other generated columns. Any functions and
 244           operators used must be immutable. References to other tables are
 245           not allowed.
 246
 247    server_name
 248           The name of an existing foreign server to use for the foreign
 249           table. For details on defining a server, see CREATE SERVER.
 250
 251    OPTIONS ( option 'value' [, ...] )
 252           Options to be associated with the new foreign table or one of
 253           its columns. The allowed option names and values are specific to
 254           each foreign data wrapper and are validated using the
 255           foreign-data wrapper's validator function. Duplicate option
 256           names are not allowed (although it's OK for a table option and a
 257           column option to have the same name).
 258
 259 Notes
 260
 261    Constraints on foreign tables (such as CHECK or NOT NULL clauses) are
 262    not enforced by the core PostgreSQL system, and most foreign data
 263    wrappers do not attempt to enforce them either; that is, the constraint
 264    is simply assumed to hold true. There would be little point in such
 265    enforcement since it would only apply to rows inserted or updated via
 266    the foreign table, and not to rows modified by other means, such as
 267    directly on the remote server. Instead, a constraint attached to a
 268    foreign table should represent a constraint that is being enforced by
 269    the remote server.
 270
 271    Some special-purpose foreign data wrappers might be the only access
 272    mechanism for the data they access, and in that case it might be
 273    appropriate for the foreign data wrapper itself to perform constraint
 274    enforcement. But you should not assume that a wrapper does that unless
 275    its documentation says so.
 276
 277    Although PostgreSQL does not attempt to enforce constraints on foreign
 278    tables, it does assume that they are correct for purposes of query
 279    optimization. If there are rows visible in the foreign table that do
 280    not satisfy a declared constraint, queries on the table might produce
 281    errors or incorrect answers. It is the user's responsibility to ensure
 282    that the constraint definition matches reality.
 283
 284 Caution
 285
 286    When a foreign table is used as a partition of a partitioned table,
 287    there is an implicit constraint that its contents must satisfy the
 288    partitioning rule. Again, it is the user's responsibility to ensure
 289    that that is true, which is best done by installing a matching
 290    constraint on the remote server.
 291
 292    Within a partitioned table containing foreign-table partitions, an
 293    UPDATE that changes the partition key value can cause a row to be moved
 294    from a local partition to a foreign-table partition, provided the
 295    foreign data wrapper supports tuple routing. However, it is not
 296    currently possible to move a row from a foreign-table partition to
 297    another partition. An UPDATE that would require doing that will fail
 298    due to the partitioning constraint, assuming that that is properly
 299    enforced by the remote server.
 300
 301    Similar considerations apply to generated columns. Stored generated
 302    columns are computed on insert or update on the local PostgreSQL server
 303    and handed to the foreign-data wrapper for writing out to the foreign
 304    data store, but it is not enforced that a query of the foreign table
 305    returns values for stored generated columns that are consistent with
 306    the generation expression. Again, this might result in incorrect query
 307    results.
 308
 309 Examples
 310
 311    Create foreign table films, which will be accessed through the server
 312    film_server:
 313 CREATE FOREIGN TABLE films (
 314     code        char(5) NOT NULL,
 315     title       varchar(40) NOT NULL,
 316     did         integer NOT NULL,
 317     date_prod   date,
 318     kind        varchar(10),
 319     len         interval hour to minute
 320 )
 321 SERVER film_server;
 322
 323    Create foreign table measurement_y2016m07, which will be accessed
 324    through the server server_07, as a partition of the range partitioned
 325    table measurement:
 326 CREATE FOREIGN TABLE measurement_y2016m07
 327     PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
 328     SERVER server_07;
 329
 330 Compatibility
 331
 332    The CREATE FOREIGN TABLE command largely conforms to the SQL standard;
 333    however, much as with CREATE TABLE, NULL constraints and zero-column
 334    foreign tables are permitted. The ability to specify column default
 335    values is also a PostgreSQL extension. Table inheritance, in the form
 336    defined by PostgreSQL, is nonstandard. The LIKE clause, as supported in
 337    this command, is nonstandard.
 338
 339 See Also
 340
 341    ALTER FOREIGN TABLE, DROP FOREIGN TABLE, CREATE TABLE, CREATE SERVER,
 342    IMPORT FOREIGN SCHEMA