1CREATE TABLE(7) PostgreSQL 9.2.24 Documentation CREATE TABLE(7)
2
6 CREATE_TABLE - define a new table
7
9 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
10 { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
11 | table_constraint
12 | LIKE source_table [ like_option ... ] }
13 [, ... ]
14 ] )
15 [ INHERITS ( parent_table [, ... ] ) ]
16 [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
17 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
18 [ TABLESPACE tablespace_name ]
19 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
21 OF type_name [ (
22 { column_name WITH OPTIONS [ column_constraint [ ... ] ]
23 | table_constraint }
24 [, ... ]
25 ) ]
26 [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
27 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
28 [ TABLESPACE tablespace_name ]
29 where column_constraint is:
31 [ CONSTRAINT constraint_name ]
33 { NOT NULL |
34 NULL |
35 CHECK ( expression ) [ NO INHERIT ] |
36 DEFAULT default_expr |
37 UNIQUE index_parameters |
38 PRIMARY KEY index_parameters |
39 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
40 [ ON DELETE action ] [ ON UPDATE action ] }
41 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
42 and table_constraint is:
44 [ CONSTRAINT constraint_name ]
46 { CHECK ( expression ) [ NO INHERIT ] |
47 UNIQUE ( column_name [, ... ] ) index_parameters |
48 PRIMARY KEY ( column_name [, ... ] ) index_parameters |
49 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
50 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
51 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
52 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
53 and like_option is:
55 { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
57 index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
59 [ WITH ( storage_parameter [= value] [, ... ] ) ]
61 [ USING INDEX TABLESPACE tablespace_name ]
62 exclude_element in an EXCLUDE constraint is:
64 { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
66
68 CREATE TABLE will create a new, initially empty table in the current
69 database. The table will be owned by the user issuing the command.
70 If a schema name is given (for example, CREATE TABLE myschema.mytable
72 ...) then the table is created in the specified schema. Otherwise it is
73 created in the current schema. Temporary tables exist in a special
74 schema, so a schema name cannot be given when creating a temporary
75 table. The name of the table must be distinct from the name of any
76 other table, sequence, index, view, or foreign table in the same
77 schema.
78 CREATE TABLE also automatically creates a data type that represents the
80 composite type corresponding to one row of the table. Therefore, tables
81 cannot have the same name as any existing data type in the same schema.
82 The optional constraint clauses specify constraints (tests) that new or
84 updated rows must satisfy for an insert or update operation to succeed.
85 A constraint is an SQL object that helps define the set of valid values
86 in the table in various ways.
87 There are two ways to define constraints: table constraints and column
89 constraints. A column constraint is defined as part of a column
90 definition. A table constraint definition is not tied to a particular
91 column, and it can encompass more than one column. Every column
92 constraint can also be written as a table constraint; a column
93 constraint is only a notational convenience for use when the constraint
94 only affects one column.
95 To be able to create a table, you must have USAGE privilege on all
97 column types or the type in the OF clause, respectively.
98
100 TEMPORARY or TEMP
101 If specified, the table is created as a temporary table. Temporary
102 tables are automatically dropped at the end of a session, or
103 optionally at the end of the current transaction (see ON COMMIT
104 below). Existing permanent tables with the same name are not
105 visible to the current session while the temporary table exists,
106 unless they are referenced with schema-qualified names. Any indexes
107 created on a temporary table are automatically temporary as well.
108 The autovacuum daemon cannot access and therefore cannot vacuum or
110 analyze temporary tables. For this reason, appropriate vacuum and
111 analyze operations should be performed via session SQL commands.
112 For example, if a temporary table is going to be used in complex
113 queries, it is wise to run ANALYZE on the temporary table after it
114 is populated.
115 Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
117 TEMP. This presently makes no difference in PostgreSQL and is
118 deprecated; see COMPATIBILITY.
119 UNLOGGED
121 If specified, the table is created as an unlogged table. Data
122 written to unlogged tables is not written to the write-ahead log
123 (see Chapter 29, Reliability and the Write-Ahead Log, in the
124 documentation), which makes them considerably faster than ordinary
125 tables. However, they are not crash-safe: an unlogged table is
126 automatically truncated after a crash or unclean shutdown. The
127 contents of an unlogged table are also not replicated to standby
128 servers. Any indexes created on an unlogged table are automatically
129 unlogged as well; however, unlogged GiST indexes are currently not
130 supported and cannot be created on an unlogged table.
131 IF NOT EXISTS
133 Do not throw an error if a relation with the same name already
134 exists. A notice is issued in this case. Note that there is no
135 guarantee that the existing relation is anything like the one that
136 would have been created.
137 table_name
139 The name (optionally schema-qualified) of the table to be created.
140 OF type_name
142 Creates a typed table, which takes its structure from the specified
143 composite type (name optionally schema-qualified). A typed table is
144 tied to its type; for example the table will be dropped if the type
145 is dropped (with DROP TYPE ... CASCADE).
146 When a typed table is created, then the data types of the columns
148 are determined by the underlying composite type and are not
149 specified by the CREATE TABLE command. But the CREATE TABLE command
150 can add defaults and constraints to the table and can specify
151 storage parameters.
152 column_name
154 The name of a column to be created in the new table.
155 data_type
157 The data type of the column. This can include array specifiers. For
158 more information on the data types supported by PostgreSQL, refer
159 to Chapter 8, Data Types, in the documentation.
160 COLLATE collation
162 The COLLATE clause assigns a collation to the column (which must be
163 of a collatable data type). If not specified, the column data
164 type's default collation is used.
165 INHERITS ( parent_table [, ... ] )
167 The optional INHERITS clause specifies a list of tables from which
168 the new table automatically inherits all columns.
169 Use of INHERITS creates a persistent relationship between the new
171 child table and its parent table(s). Schema modifications to the
172 parent(s) normally propagate to children as well, and by default
173 the data of the child table is included in scans of the parent(s).
174 If the same column name exists in more than one parent table, an
176 error is reported unless the data types of the columns match in
177 each of the parent tables. If there is no conflict, then the
178 duplicate columns are merged to form a single column in the new
179 table. If the column name list of the new table contains a column
180 name that is also inherited, the data type must likewise match the
181 inherited column(s), and the column definitions are merged into
182 one. If the new table explicitly specifies a default value for the
183 column, this default overrides any defaults from inherited
184 declarations of the column. Otherwise, any parents that specify
185 default values for the column must all specify the same default, or
186 an error will be reported.
187 CHECK constraints are merged in essentially the same way as
189 columns: if multiple parent tables and/or the new table definition
190 contain identically-named CHECK constraints, these constraints must
191 all have the same check expression, or an error will be reported.
192 Constraints having the same name and expression will be merged into
193 one copy. A constraint marked NO INHERIT in a parent will not be
194 considered. Notice that an unnamed CHECK constraint in the new
195 table will never be merged, since a unique name will always be
196 chosen for it.
197 Column STORAGE settings are also copied from parent tables.
199 LIKE source_table [ like_option ... ]
201 The LIKE clause specifies a table from which the new table
202 automatically copies all column names, their data types, and their
203 not-null constraints.
204 Unlike INHERITS, the new table and original table are completely
206 decoupled after creation is complete. Changes to the original table
207 will not be applied to the new table, and it is not possible to
208 include data of the new table in scans of the original table.
209 Default expressions for the copied column definitions will be
211 copied only if INCLUDING DEFAULTS is specified. The default
212 behavior is to exclude default expressions, resulting in the copied
213 columns in the new table having null defaults. Note that copying
214 defaults that call database-modification functions, such as
215 nextval, may create a functional linkage between the original and
216 new tables.
217 Not-null constraints are always copied to the new table. CHECK
219 constraints will be copied only if INCLUDING CONSTRAINTS is
220 specified. No distinction is made between column constraints and
221 table constraints.
222 Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the
224 original table will be created on the new table only if INCLUDING
225 INDEXES is specified. Names for the new indexes and constraints are
226 chosen according to the default rules, regardless of how the
227 originals were named. (This behavior avoids possible duplicate-name
228 failures for the new indexes.)
229 STORAGE settings for the copied column definitions will be copied
231 only if INCLUDING STORAGE is specified. The default behavior is to
232 exclude STORAGE settings, resulting in the copied columns in the
233 new table having type-specific default settings. For more on
234 STORAGE settings, see Section 56.2, “TOAST”, in the documentation.
235 Comments for the copied columns, constraints, and indexes will be
237 copied only if INCLUDING COMMENTS is specified. The default
238 behavior is to exclude comments, resulting in the copied columns
239 and constraints in the new table having no comments.
240 INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS
242 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING
243 COMMENTS.
244 Note that unlike INHERITS, columns and constraints copied by LIKE
246 are not merged with similarly named columns and constraints. If the
247 same name is specified explicitly or in another LIKE clause, an
248 error is signaled.
249 The LIKE clause can also be used to copy column definitions from
251 views, foreign tables, or composite types. Inapplicable options
252 (e.g., INCLUDING INDEXES from a view) are ignored.
253 CONSTRAINT constraint_name
255 An optional name for a column or table constraint. If the
256 constraint is violated, the constraint name is present in error
257 messages, so constraint names like col must be positive can be used
258 to communicate helpful constraint information to client
259 applications. (Double-quotes are needed to specify constraint names
260 that contain spaces.) If a constraint name is not specified, the
261 system generates a name.
262 NOT NULL
264 The column is not allowed to contain null values.
265 NULL
267 The column is allowed to contain null values. This is the default.
268 This clause is only provided for compatibility with non-standard
270 SQL databases. Its use is discouraged in new applications.
271 CHECK ( expression ) [ NO INHERIT ]
273 The CHECK clause specifies an expression producing a Boolean result
274 which new or updated rows must satisfy for an insert or update
275 operation to succeed. Expressions evaluating to TRUE or UNKNOWN
276 succeed. Should any row of an insert or update operation produce a
277 FALSE result an error exception is raised and the insert or update
278 does not alter the database. A check constraint specified as a
279 column constraint should reference that column's value only, while
280 an expression appearing in a table constraint can reference
281 multiple columns.
282 Currently, CHECK expressions cannot contain subqueries nor refer to
284 variables other than columns of the current row.
285 A constraint marked with NO INHERIT will not propagate to child
287 tables.
288 DEFAULT default_expr
290 The DEFAULT clause assigns a default data value for the column
291 whose column definition it appears within. The value is any
292 variable-free expression (subqueries and cross-references to other
293 columns in the current table are not allowed). The data type of the
294 default expression must match the data type of the column.
295 The default expression will be used in any insert operation that
297 does not specify a value for the column. If there is no default for
298 a column, then the default is null.
299 UNIQUE (column constraint), UNIQUE ( column_name [, ... ] ) (table
301 constraint)
302 The UNIQUE constraint specifies that a group of one or more columns
303 of a table can contain only unique values. The behavior of the
304 unique table constraint is the same as that for column constraints,
305 with the additional capability to span multiple columns.
306 For the purpose of a unique constraint, null values are not
308 considered equal.
309 Each unique table constraint must name a set of columns that is
311 different from the set of columns named by any other unique or
312 primary key constraint defined for the table. (Otherwise it would
313 just be the same constraint listed twice.)
314 PRIMARY KEY (column constraint), PRIMARY KEY ( column_name [, ... ] )
316 (table constraint)
317 The PRIMARY KEY constraint specifies that a column or columns of a
318 table can contain only unique (non-duplicate), nonnull values. Only
319 one primary key can be specified for a table, whether as a column
320 constraint or a table constraint.
321 The primary key constraint should name a set of columns that is
323 different from the set of columns named by any unique constraint
324 defined for the same table. (Otherwise, the unique constraint is
325 redundant and will be discarded.)
326 PRIMARY KEY enforces the same data constraints as a combination of
328 UNIQUE and NOT NULL, but identifying a set of columns as the
329 primary key also provides metadata about the design of the schema,
330 since a primary key implies that other tables can rely on this set
331 of columns as a unique identifier for rows.
332 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ]
334 ) index_parameters [ WHERE ( predicate ) ]
335 The EXCLUDE clause defines an exclusion constraint, which
336 guarantees that if any two rows are compared on the specified
337 column(s) or expression(s) using the specified operator(s), not all
338 of these comparisons will return TRUE. If all of the specified
339 operators test for equality, this is equivalent to a UNIQUE
340 constraint, although an ordinary unique constraint will be faster.
341 However, exclusion constraints can specify constraints that are
342 more general than simple equality. For example, you can specify a
343 constraint that no two rows in the table contain overlapping
344 circles (see Section 8.8, “Geometric Types”, in the documentation)
345 by using the && operator.
346 Exclusion constraints are implemented using an index, so each
348 specified operator must be associated with an appropriate operator
349 class (see Section 11.9, “Operator Classes and Operator Families”,
350 in the documentation) for the index access method index_method. The
351 operators are required to be commutative. Each exclude_element can
352 optionally specify an operator class and/or ordering options; these
353 are described fully under CREATE INDEX (CREATE_INDEX(7)).
354 The access method must support amgettuple (see Chapter 52, Index
356 Access Method Interface Definition, in the documentation); at
357 present this means GIN cannot be used. Although it's allowed, there
358 is little point in using B-tree or hash indexes with an exclusion
359 constraint, because this does nothing that an ordinary unique
360 constraint doesn't do better. So in practice the access method will
361 always be GiST or SP-GiST.
362 The predicate allows you to specify an exclusion constraint on a
364 subset of the table; internally this creates a partial index. Note
365 that parentheses are required around the predicate.
366 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
368 action ] [ ON UPDATE action ] (column constraint), FOREIGN KEY (
369 column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [
370 MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table
371 constraint)
372 These clauses specify a foreign key constraint, which requires that
373 a group of one or more columns of the new table must only contain
374 values that match values in the referenced column(s) of some row of
375 the referenced table. If refcolumn is omitted, the primary key of
376 the reftable is used. The referenced columns must be the columns of
377 a non-deferrable unique or primary key constraint in the referenced
378 table. Note that foreign key constraints cannot be defined between
379 temporary tables and permanent tables.
380 A value inserted into the referencing column(s) is matched against
382 the values of the referenced table and referenced columns using the
383 given match type. There are three match types: MATCH FULL, MATCH
384 PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL
385 will not allow one column of a multicolumn foreign key to be null
386 unless all foreign key columns are null. MATCH SIMPLE allows some
387 foreign key columns to be null while other parts of the foreign key
388 are not null. MATCH PARTIAL is not yet implemented.
389 In addition, when the data in the referenced columns is changed,
391 certain actions are performed on the data in this table's columns.
392 The ON DELETE clause specifies the action to perform when a
393 referenced row in the referenced table is being deleted. Likewise,
394 the ON UPDATE clause specifies the action to perform when a
395 referenced column in the referenced table is being updated to a new
396 value. If the row is updated, but the referenced column is not
397 actually changed, no action is done. Referential actions other than
398 the NO ACTION check cannot be deferred, even if the constraint is
399 declared deferrable. There are the following possible actions for
400 each clause:
401 NO ACTION
403 Produce an error indicating that the deletion or update would
404 create a foreign key constraint violation. If the constraint is
405 deferred, this error will be produced at constraint check time
406 if there still exist any referencing rows. This is the default
407 action.
408 RESTRICT
410 Produce an error indicating that the deletion or update would
411 create a foreign key constraint violation. This is the same as
412 NO ACTION except that the check is not deferrable.
413 CASCADE
415 Delete any rows referencing the deleted row, or update the
416 value of the referencing column to the new value of the
417 referenced column, respectively.
418 SET NULL
420 Set the referencing column(s) to null.
421 SET DEFAULT
423 Set the referencing column(s) to their default values.
424 If the referenced column(s) are changed frequently, it might be
426 wise to add an index to the foreign key column so that referential
427 actions associated with the foreign key column can be performed
428 more efficiently.
429 DEFERRABLE, NOT DEFERRABLE
431 This controls whether the constraint can be deferred. A constraint
432 that is not deferrable will be checked immediately after every
433 command. Checking of constraints that are deferrable can be
434 postponed until the end of the transaction (using the SET
435 CONSTRAINTS (SET_CONSTRAINTS(7)) command). NOT DEFERRABLE is the
436 default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and
437 REFERENCES (foreign key) constraints accept this clause. NOT NULL
438 and CHECK constraints are not deferrable.
439 INITIALLY IMMEDIATE, INITIALLY DEFERRED
441 If a constraint is deferrable, this clause specifies the default
442 time to check the constraint. If the constraint is INITIALLY
443 IMMEDIATE, it is checked after each statement. This is the default.
444 If the constraint is INITIALLY DEFERRED, it is checked only at the
445 end of the transaction. The constraint check time can be altered
446 with the SET CONSTRAINTS (SET_CONSTRAINTS(7)) command.
447 WITH ( storage_parameter [= value] [, ... ] )
449 This clause specifies optional storage parameters for a table or
450 index; see Storage Parameters for more information. The WITH clause
451 for a table can also include OIDS=TRUE (or just OIDS) to specify
452 that rows of the new table should have OIDs (object identifiers)
453 assigned to them, or OIDS=FALSE to specify that the rows should not
454 have OIDs. If OIDS is not specified, the default setting depends
455 upon the default_with_oids configuration parameter. (If the new
456 table inherits from any tables that have OIDs, then OIDS=TRUE is
457 forced even if the command says OIDS=FALSE.)
458 If OIDS=FALSE is specified or implied, the new table does not store
460 OIDs and no OID will be assigned for a row inserted into it. This
461 is generally considered worthwhile, since it will reduce OID
462 consumption and thereby postpone the wraparound of the 32-bit OID
463 counter. Once the counter wraps around, OIDs can no longer be
464 assumed to be unique, which makes them considerably less useful. In
465 addition, excluding OIDs from a table reduces the space required to
466 store the table on disk by 4 bytes per row (on most machines),
467 slightly improving performance.
468 To remove OIDs from a table after it has been created, use ALTER
470 TABLE (ALTER_TABLE(7)).
471 WITH OIDS, WITHOUT OIDS
473 These are obsolescent syntaxes equivalent to WITH (OIDS) and WITH
474 (OIDS=FALSE), respectively. If you wish to give both an OIDS
475 setting and storage parameters, you must use the WITH ( ... )
476 syntax; see above.
477 ON COMMIT
479 The behavior of temporary tables at the end of a transaction block
480 can be controlled using ON COMMIT. The three options are:
481 PRESERVE ROWS
483 No special action is taken at the ends of transactions. This is
484 the default behavior.
485 DELETE ROWS
487 All rows in the temporary table will be deleted at the end of
488 each transaction block. Essentially, an automatic TRUNCATE(7)
489 is done at each commit.
490 DROP
492 The temporary table will be dropped at the end of the current
493 transaction block.
494 TABLESPACE tablespace_name
496 The tablespace_name is the name of the tablespace in which the new
497 table is to be created. If not specified, default_tablespace is
498 consulted, or temp_tablespaces if the table is temporary.
499 USING INDEX TABLESPACE tablespace_name
501 This clause allows selection of the tablespace in which the index
502 associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will
503 be created. If not specified, default_tablespace is consulted, or
504 temp_tablespaces if the table is temporary.
505 Storage Parameters
507 The WITH clause can specify storage parameters for tables, and for
508 indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint.
509 Storage parameters for indexes are documented in CREATE INDEX
510 (CREATE_INDEX(7)). The storage parameters currently available for
511 tables are listed below. For each parameter, unless noted, there is an
512 additional parameter with the same name prefixed with toast., which can
513 be used to control the behavior of the table's secondary TOAST table,
514 if any (see Section 56.2, “TOAST”, in the documentation for more
515 information about TOAST). Note that the TOAST table inherits the
516 autovacuum_* values from its parent table, if there are no
517 toast.autovacuum_* settings set.
518 fillfactor (integer)
520 The fillfactor for a table is a percentage between 10 and 100. 100
521 (complete packing) is the default. When a smaller fillfactor is
522 specified, INSERT operations pack table pages only to the indicated
523 percentage; the remaining space on each page is reserved for
524 updating rows on that page. This gives UPDATE a chance to place the
525 updated copy of a row on the same page as the original, which is
526 more efficient than placing it on a different page. For a table
527 whose entries are never updated, complete packing is the best
528 choice, but in heavily updated tables smaller fillfactors are
529 appropriate. This parameter cannot be set for TOAST tables.
530 autovacuum_enabled, toast.autovacuum_enabled (boolean)
532 Enables or disables the autovacuum daemon on a particular table. If
533 true, the autovacuum daemon will initiate a VACUUM operation on a
534 particular table when the number of updated or deleted tuples
535 exceeds autovacuum_vacuum_threshold plus
536 autovacuum_vacuum_scale_factor times the number of live tuples
537 currently estimated to be in the relation. Similarly, it will
538 initiate an ANALYZE operation when the number of inserted, updated
539 or deleted tuples exceeds autovacuum_analyze_threshold plus
540 autovacuum_analyze_scale_factor times the number of live tuples
541 currently estimated to be in the relation. If false, this table
542 will not be autovacuumed, except to prevent transaction Id
543 wraparound. See Section 23.1.5, “Preventing Transaction ID
544 Wraparound Failures”, in the documentation for more about
545 wraparound prevention. Observe that this variable inherits its
546 value from the autovacuum setting.
547 autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
549 (integer)
550 Minimum number of updated or deleted tuples before initiate a
551 VACUUM operation on a particular table.
552 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
554 (float4)
555 Multiplier for reltuples to add to autovacuum_vacuum_threshold.
556 autovacuum_analyze_threshold (integer)
558 Minimum number of inserted, updated, or deleted tuples before
559 initiate an ANALYZE operation on a particular table.
560 autovacuum_analyze_scale_factor (float4)
562 Multiplier for reltuples to add to autovacuum_analyze_threshold.
563 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
565 (integer)
566 Custom autovacuum_vacuum_cost_delay parameter.
567 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
569 (integer)
570 Custom autovacuum_vacuum_cost_limit parameter.
571 autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
573 Custom vacuum_freeze_min_age parameter. Note that autovacuum will
574 ignore attempts to set a per-table autovacuum_freeze_min_age larger
575 than the half system-wide autovacuum_freeze_max_age setting.
576 autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
578 Custom autovacuum_freeze_max_age parameter. Note that autovacuum
579 will ignore attempts to set a per-table autovacuum_freeze_max_age
580 larger than the system-wide setting (it can only be set smaller).
581 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
583 (integer)
584 Custom vacuum_freeze_table_age parameter.
585
587 Using OIDs in new applications is not recommended: where possible,
588 using a SERIAL or other sequence generator as the table's primary key
589 is preferred. However, if your application does make use of OIDs to
590 identify specific rows of a table, it is recommended to create a unique
591 constraint on the oid column of that table, to ensure that OIDs in the
592 table will indeed uniquely identify rows even after counter wraparound.
593 Avoid assuming that OIDs are unique across tables; if you need a
594 database-wide unique identifier, use the combination of tableoid and
595 row OID for the purpose.
596 Tip
598 The use of OIDS=FALSE is not recommended for tables with no primary
599 key, since without either an OID or a unique data key, it is
600 difficult to identify specific rows.
601 PostgreSQL automatically creates an index for each unique constraint
603 and primary key constraint to enforce uniqueness. Thus, it is not
604 necessary to create an index explicitly for primary key columns. (See
605 CREATE INDEX (CREATE_INDEX(7)) for more information.)
606 Unique constraints and primary keys are not inherited in the current
608 implementation. This makes the combination of inheritance and unique
609 constraints rather dysfunctional.
610 A table cannot have more than 1600 columns. (In practice, the effective
612 limit is usually lower because of tuple-length constraints.)
613
615 Create table films and table distributors:
616 CREATE TABLE films (
618 code char(5) CONSTRAINT firstkey PRIMARY KEY,
619 title varchar(40) NOT NULL,
620 did integer NOT NULL,
621 date_prod date,
622 kind varchar(10),
623 len interval hour to minute
624 );
625 CREATE TABLE distributors (
627 did integer PRIMARY KEY DEFAULT nextval('serial'),
628 name varchar(40) NOT NULL CHECK (name <> '')
629 );
630 Create a table with a 2-dimensional array:
632 CREATE TABLE array_int (
634 vector int[][]
635 );
636 Define a unique table constraint for the table films. Unique table
638 constraints can be defined on one or more columns of the table:
639 CREATE TABLE films (
641 code char(5),
642 title varchar(40),
643 did integer,
644 date_prod date,
645 kind varchar(10),
646 len interval hour to minute,
647 CONSTRAINT production UNIQUE(date_prod)
648 );
649 Define a check column constraint:
651 CREATE TABLE distributors (
653 did integer CHECK (did > 100),
654 name varchar(40)
655 );
656 Define a check table constraint:
658 CREATE TABLE distributors (
660 did integer,
661 name varchar(40)
662 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
663 );
664 Define a primary key table constraint for the table films:
666 CREATE TABLE films (
668 code char(5),
669 title varchar(40),
670 did integer,
671 date_prod date,
672 kind varchar(10),
673 len interval hour to minute,
674 CONSTRAINT code_title PRIMARY KEY(code,title)
675 );
676 Define a primary key constraint for table distributors. The following
678 two examples are equivalent, the first using the table constraint
679 syntax, the second the column constraint syntax:
680 CREATE TABLE distributors (
682 did integer,
683 name varchar(40),
684 PRIMARY KEY(did)
685 );
686 CREATE TABLE distributors (
688 did integer PRIMARY KEY,
689 name varchar(40)
690 );
691 Assign a literal constant default value for the column name, arrange
693 for the default value of column did to be generated by selecting the
694 next value of a sequence object, and make the default value of modtime
695 be the time at which the row is inserted:
696 CREATE TABLE distributors (
698 name varchar(40) DEFAULT 'Luso Films',
699 did integer DEFAULT nextval('distributors_serial'),
700 modtime timestamp DEFAULT current_timestamp
701 );
702 Define two NOT NULL column constraints on the table distributors, one
704 of which is explicitly given a name:
705 CREATE TABLE distributors (
707 did integer CONSTRAINT no_null NOT NULL,
708 name varchar(40) NOT NULL
709 );
710 Define a unique constraint for the name column:
712 CREATE TABLE distributors (
714 did integer,
715 name varchar(40) UNIQUE
716 );
717 The same, specified as a table constraint:
719 CREATE TABLE distributors (
721 did integer,
722 name varchar(40),
723 UNIQUE(name)
724 );
725 Create the same table, specifying 70% fill factor for both the table
727 and its unique index:
728 CREATE TABLE distributors (
730 did integer,
731 name varchar(40),
732 UNIQUE(name) WITH (fillfactor=70)
733 )
734 WITH (fillfactor=70);
735 Create table circles with an exclusion constraint that prevents any two
737 circles from overlapping:
738 CREATE TABLE circles (
740 c circle,
741 EXCLUDE USING gist (c WITH &&)
742 );
743 Create table cinemas in tablespace diskvol1:
745 CREATE TABLE cinemas (
747 id serial,
748 name text,
749 location text
750 ) TABLESPACE diskvol1;
751 Create a composite type and a typed table:
753 CREATE TYPE employee_type AS (name text, salary numeric);
755 CREATE TABLE employees OF employee_type (
757 PRIMARY KEY (name),
758 salary WITH OPTIONS DEFAULT 1000
759 );
760
762 The CREATE TABLE command conforms to the SQL standard, with exceptions
763 listed below.
764 Temporary Tables
766 Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
767 standard, the effect is not the same. In the standard, temporary tables
768 are defined just once and automatically exist (starting with empty
769 contents) in every session that needs them. PostgreSQL instead
770 requires each session to issue its own CREATE TEMPORARY TABLE command
771 for each temporary table to be used. This allows different sessions to
772 use the same temporary table name for different purposes, whereas the
773 standard's approach constrains all instances of a given temporary table
774 name to have the same table structure.
775 The standard's definition of the behavior of temporary tables is widely
777 ignored. PostgreSQL's behavior on this point is similar to that of
778 several other SQL databases.
779 The SQL standard also distinguishes between global and local temporary
781 tables, where a local temporary table has a separate set of contents
782 for each SQL module within each session, though its definition is still
783 shared across sessions. Since PostgreSQL does not support SQL modules,
784 this distinction is not relevant in PostgreSQL.
785 For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
787 keywords in a temporary table declaration, but they currently have no
788 effect. Use of these keywords is discouraged, since future versions of
789 PostgreSQL might adopt a more standard-compliant interpretation of
790 their meaning.
791 The ON COMMIT clause for temporary tables also resembles the SQL
793 standard, but has some differences. If the ON COMMIT clause is omitted,
794 SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
795 However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
796 The ON COMMIT DROP option does not exist in SQL.
797 Non-deferred Uniqueness Constraints
799 When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
800 checks for uniqueness immediately whenever a row is inserted or
801 modified. The SQL standard says that uniqueness should be enforced only
802 at the end of the statement; this makes a difference when, for example,
803 a single command updates multiple key values. To obtain
804 standard-compliant behavior, declare the constraint as DEFERRABLE but
805 not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
806 significantly slower than immediate uniqueness checking.
807 Column Check Constraints
809 The SQL standard says that CHECK column constraints can only refer to
810 the column they apply to; only CHECK table constraints can refer to
811 multiple columns. PostgreSQL does not enforce this restriction; it
812 treats column and table check constraints alike.
813 EXCLUDE Constraint
815 The EXCLUDE constraint type is a PostgreSQL extension.
816 NULL “Constraint”
818 The NULL“constraint” (actually a non-constraint) is a PostgreSQL
819 extension to the SQL standard that is included for compatibility with
820 some other database systems (and for symmetry with the NOT NULL
821 constraint). Since it is the default for any column, its presence is
822 simply noise.
823 Inheritance
825 Multiple inheritance via the INHERITS clause is a PostgreSQL language
826 extension. SQL:1999 and later define single inheritance using a
827 different syntax and different semantics. SQL:1999-style inheritance is
828 not yet supported by PostgreSQL.
829 Zero-column Tables
831 PostgreSQL allows a table of no columns to be created (for example,
832 CREATE TABLE foo();). This is an extension from the SQL standard, which
833 does not allow zero-column tables. Zero-column tables are not in
834 themselves very useful, but disallowing them creates odd special cases
835 for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
836 restriction.
837 LIKE Clause
839 While a LIKE clause exists in the SQL standard, many of the options
840 that PostgreSQL accepts for it are not in the standard, and some of the
841 standard's options are not implemented by PostgreSQL.
842 WITH Clause
844 The WITH clause is a PostgreSQL extension; neither storage parameters
845 nor OIDs are in the standard.
846 Tablespaces
848 The PostgreSQL concept of tablespaces is not part of the standard.
849 Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
850 extensions.
851 Typed Tables
853 Typed tables implement a subset of the SQL standard. According to the
854 standard, a typed table has columns corresponding to the underlying
855 composite type as well as one other column that is the
856 “self-referencing column”. PostgreSQL does not support these
857 self-referencing columns explicitly, but the same effect can be had
858 using the OID feature.
859
861 ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE
862 TABLESPACE (CREATE_TABLESPACE(7)), CREATE TYPE (CREATE_TYPE(7))
863PostgreSQL 9.2.24 2017-11-06 CREATE TABLE(7)