2
.\" Title: CREATE TABLE
3
.\" Author: The PostgreSQL Global Development Group
4
.\" Generator: DocBook XSL Stylesheets v1.75.1 <http://docbook.sf.net/>
6
.\" Manual: PostgreSQL 9.1beta1 Documentation
7
.\" Source: PostgreSQL 9.1beta1
10
.TH "CREATE TABLE" "7" "2011-04-27" "PostgreSQL 9.1beta1" "PostgreSQL 9.1beta1 Documentation"
11
.\" -----------------------------------------------------------------
12
.\" * set default formatting
13
.\" -----------------------------------------------------------------
14
.\" disable hyphenation
16
.\" disable justification (adjust text to left margin only)
18
.\" -----------------------------------------------------------------
19
.\" * MAIN CONTENT STARTS HERE *
20
.\" -----------------------------------------------------------------
22
CREATE_TABLE \- define a new table
27
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] \fItable_name\fR ( [
28
{ \fIcolumn_name\fR \fIdata_type\fR [ COLLATE \fIcollation\fR ] [ \fIcolumn_constraint\fR [ \&.\&.\&. ] ]
29
| \fItable_constraint\fR
30
| LIKE \fIparent_table\fR [ \fIlike_option\fR \&.\&.\&. ] }
33
[ INHERITS ( \fIparent_table\fR [, \&.\&.\&. ] ) ]
34
[ WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) | WITH OIDS | WITHOUT OIDS ]
35
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
36
[ TABLESPACE \fItablespace\fR ]
38
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] \fItable_name\fR
39
OF \fItype_name\fR [ (
40
{ \fIcolumn_name\fR WITH OPTIONS [ \fIcolumn_constraint\fR [ \&.\&.\&. ] ]
41
| \fItable_constraint\fR }
44
[ WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) | WITH OIDS | WITHOUT OIDS ]
45
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
46
[ TABLESPACE \fItablespace\fR ]
48
where \fIcolumn_constraint\fR is:
50
[ CONSTRAINT \fIconstraint_name\fR ]
53
CHECK ( \fIexpression\fR ) |
54
DEFAULT \fIdefault_expr\fR |
55
UNIQUE \fIindex_parameters\fR |
56
PRIMARY KEY \fIindex_parameters\fR |
57
REFERENCES \fIreftable\fR [ ( \fIrefcolumn\fR ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
58
[ ON DELETE \fIaction\fR ] [ ON UPDATE \fIaction\fR ] }
59
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
61
and \fItable_constraint\fR is:
63
[ CONSTRAINT \fIconstraint_name\fR ]
64
{ CHECK ( \fIexpression\fR ) |
65
UNIQUE ( \fIcolumn_name\fR [, \&.\&.\&. ] ) \fIindex_parameters\fR |
66
PRIMARY KEY ( \fIcolumn_name\fR [, \&.\&.\&. ] ) \fIindex_parameters\fR |
67
EXCLUDE [ USING \fIindex_method\fR ] ( \fIexclude_element\fR WITH \fIoperator\fR [, \&.\&.\&. ] ) \fIindex_parameters\fR [ WHERE ( \fIpredicate\fR ) ] |
68
FOREIGN KEY ( \fIcolumn_name\fR [, \&.\&.\&. ] ) REFERENCES \fIreftable\fR [ ( \fIrefcolumn\fR [, \&.\&.\&. ] ) ]
69
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE \fIaction\fR ] [ ON UPDATE \fIaction\fR ] }
70
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
72
and \fIlike_option\fR is:
74
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
76
\fIindex_parameters\fR in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
78
[ WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) ]
79
[ USING INDEX TABLESPACE \fItablespace\fR ]
81
\fIexclude_element\fR in an EXCLUDE constraint is:
83
{ \fIcolumn\fR | ( \fIexpression\fR ) } [ \fIopclass\fR ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
88
will create a new, initially empty table in the current database\&. The table will be owned by the user issuing the command\&.
90
If a schema name is given (for example,
91
CREATE TABLE myschema\&.mytable \&.\&.\&.) then the table is created in the specified schema\&. Otherwise it is created in the current schema\&. Temporary tables exist in a special schema, so a schema name cannot be given when creating a temporary table\&. The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in the same schema\&.
94
also automatically creates a data type that represents the composite type corresponding to one row of the table\&. Therefore, tables cannot have the same name as any existing data type in the same schema\&.
96
The optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an insert or update operation to succeed\&. A constraint is an SQL object that helps define the set of valid values in the table in various ways\&.
98
There are two ways to define constraints: table constraints and column constraints\&. A column constraint is defined as part of a column definition\&. A table constraint definition is not tied to a particular column, and it can encompass more than one column\&. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience for use when the constraint only affects one column\&.
103
If specified, the table is created as a temporary table\&. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see
105
below)\&. Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema\-qualified names\&. Any indexes created on a temporary table are automatically temporary as well\&.
109
cannot access and therefore cannot vacuum or analyze temporary tables\&. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands\&. For example, if a temporary table is going to be used in complex queries, it is wise to run
111
on the temporary table after it is populated\&.
117
can be written before
120
TEMP\&. This makes no difference in
127
If specified, the table is created as an unlogged table\&. Data written to unlogged tables is not written to the write\-ahead log (see
128
Chapter 29, Reliability and the Write-Ahead Log, in the documentation), which makes them considerably faster than ordinary tables\&. However, they are not crash\-safe: an unlogged table is automatically truncated after a crash or unclean shutdown\&. The contents of an unlogged table are also not replicated to standby servers\&. Any indexes created on an unlogged table are automatically unlogged as well; however, unlogged
130
are currently not supported and cannot be created on an unlogged table\&.
135
Do not throw an error if a relation with the same name already exists\&. A notice is issued in this case\&. Note that there is no guarantee that the existing relation is anything like the one that would have been created\&.
140
The name (optionally schema\-qualified) of the table to be created\&.
146
typed table, which takes its structure from the specified composite type (name optionally schema\-qualified)\&. A typed table is tied to its type; for example the table will be dropped if the type is dropped (with
147
DROP TYPE \&.\&.\&. CASCADE)\&.
149
When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified by the
153
command can add defaults and constraints to the table and can specify storage parameters\&.
158
The name of a column to be created in the new table\&.
163
The data type of the column\&. This can include array specifiers\&. For more information on the data types supported by
165
Chapter 8, Data Types, in the documentation\&.
168
COLLATE \fIcollation\fR
172
clause assigns a collation to the column (which must be of a collatable data type)\&. If not specified, the column data type\(aqs default collation is used\&.
175
INHERITS ( \fIparent_table\fR [, \&.\&.\&. ] )
179
clause specifies a list of tables from which the new table automatically inherits all columns\&.
183
creates a persistent relationship between the new child table and its parent table(s)\&. Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s)\&.
185
If the same column name exists in more than one parent table, an error is reported unless the data types of the columns match in each of the parent tables\&. If there is no conflict, then the duplicate columns are merged to form a single column in the new table\&. If the column name list of the new table contains a column name that is also inherited, the data type must likewise match the inherited column(s), and the column definitions are merged into one\&. If the new table explicitly specifies a default value for the column, this default overrides any defaults from inherited declarations of the column\&. Otherwise, any parents that specify default values for the column must all specify the same default, or an error will be reported\&.
188
constraints are merged in essentially the same way as columns: if multiple parent tables and/or the new table definition contain identically\-named
190
constraints, these constraints must all have the same check expression, or an error will be reported\&. Constraints having the same name and expression will be merged into one copy\&. Notice that an unnamed
192
constraint in the new table will never be merged, since a unique name will always be chosen for it\&.
196
settings are also copied from parent tables\&.
199
LIKE \fIparent_table\fR [ \fIlike_option\fR \&.\&.\&. ]
203
clause specifies a table from which the new table automatically copies all column names, their data types, and their not\-null constraints\&.
206
INHERITS, the new table and original table are completely decoupled after creation is complete\&. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table\&.
208
Default expressions for the copied column definitions will only be copied if
210
is specified\&. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having null defaults\&.
212
Not\-null constraints are always copied to the new table\&.
214
constraints will only be copied if
215
INCLUDING CONSTRAINTS
216
is specified; other types of constraints will never be copied\&. Also, no distinction is made between column constraints and table constraints \(em when constraints are requested, all check constraints are copied\&.
218
Any indexes on the original table will not be created on the new table, unless the
220
clause is specified\&.
223
settings for the copied column definitions will only be copied if
225
is specified\&. The default behavior is to exclude
227
settings, resulting in the copied columns in the new table having type\-specific default settings\&. For more on
230
Section 55.2, \(lqTOAST\(rq, in the documentation\&.
232
Comments for the copied columns, constraints, and indexes will only be copied if
234
is specified\&. The default behavior is to exclude comments, resulting in the copied columns and constraints in the new table having no comments\&.
237
is an abbreviated form of
238
INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS\&.
240
Note also that unlike
241
INHERITS, columns and constraints copied by
243
are not merged with similarly named columns and constraints\&. If the same name is specified explicitly or in another
245
clause, an error is signalled\&.
248
CONSTRAINT \fIconstraint_name\fR
250
An optional name for a column or table constraint\&. If the constraint is violated, the constraint name is present in error messages, so constraint names like
252
can be used to communicate helpful constraint information to client applications\&. (Double\-quotes are needed to specify constraint names that contain spaces\&.) If a constraint name is not specified, the system generates a name\&.
257
The column is not allowed to contain null values\&.
262
The column is allowed to contain null values\&. This is the default\&.
264
This clause is only provided for compatibility with non\-standard SQL databases\&. Its use is discouraged in new applications\&.
267
CHECK ( \fIexpression\fR )
271
clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed\&. Expressions evaluating to TRUE or UNKNOWN succeed\&. Should any row of an insert or update operation produce a FALSE result an error exception is raised and the insert or update does not alter the database\&. A check constraint specified as a column constraint should reference that column\(aqs value only, while an expression appearing in a table constraint can reference multiple columns\&.
275
expressions cannot contain subqueries nor refer to variables other than columns of the current row\&.
278
DEFAULT \fIdefault_expr\fR
282
clause assigns a default data value for the column whose column definition it appears within\&. The value is any variable\-free expression (subqueries and cross\-references to other columns in the current table are not allowed)\&. The data type of the default expression must match the data type of the column\&.
284
The default expression will be used in any insert operation that does not specify a value for the column\&. If there is no default for a column, then the default is null\&.
287
UNIQUE (column constraint), UNIQUE ( \fIcolumn_name\fR [, \&.\&.\&. ] ) (table constraint)
291
constraint specifies that a group of one or more columns of a table can contain only unique values\&. The behavior of the unique table constraint is the same as that for column constraints, with the additional capability to span multiple columns\&.
293
For the purpose of a unique constraint, null values are not considered equal\&.
295
Each unique table constraint must name a set of columns that is different from the set of columns named by any other unique or primary key constraint defined for the table\&. (Otherwise it would just be the same constraint listed twice\&.)
298
PRIMARY KEY (column constraint), PRIMARY KEY ( \fIcolumn_name\fR [, \&.\&.\&. ] ) (table constraint)
300
The primary key constraint specifies that a column or columns of a table can contain only unique (non\-duplicate), nonnull values\&. Technically,
302
is merely a combination of
305
NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows\&.
307
Only one primary key can be specified for a table, whether as a column constraint or a table constraint\&.
309
The primary key constraint should name a set of columns that is different from other sets of columns named by any unique constraint defined for the same table\&.
312
EXCLUDE [ USING \fIindex_method\fR ] ( \fIexclude_element\fR WITH \fIoperator\fR [, \&.\&.\&. ] ) \fIindex_parameters\fR [ WHERE ( \fIpredicate\fR ) ]
316
clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return
317
TRUE\&. If all of the specified operators test for equality, this is equivalent to a
319
constraint, although an ordinary unique constraint will be faster\&. However, exclusion constraints can specify constraints that are more general than simple equality\&. For example, you can specify a constraint that no two rows in the table contain overlapping circles (see
320
Section 8.8, \(lqGeometric Types\(rq, in the documentation) by using the
324
Exclusion constraints are implemented using an index, so each specified operator must be associated with an appropriate operator class (see
325
Section 11.9, \(lqOperator Classes and Operator Families\(rq, in the documentation) for the index access method
326
\fIindex_method\fR\&. The operators are required to be commutative\&. Each
327
\fIexclude_element\fR
328
can optionally specify an operator class and/or ordering options; these are described fully under
329
CREATE INDEX (\fBCREATE_INDEX\fR(7))\&.
331
The access method must support
334
Chapter 52, Index Access Method Interface Definition, in the documentation); at present this means
336
cannot be used\&. Although it\(aqs allowed, there is little point in using B\-tree or hash indexes with an exclusion constraint, because this does nothing that an ordinary unique constraint doesn\(aqt do better\&. So in practice the access method will always be
341
allows you to specify an exclusion constraint on a subset of the table; internally this creates a partial index\&. Note that parentheses are required around the predicate\&.
344
REFERENCES \fIreftable\fR [ ( \fIrefcolumn\fR ) ] [ MATCH \fImatchtype\fR ] [ ON DELETE \fIaction\fR ] [ ON UPDATE \fIaction\fR ] (column constraint), FOREIGN KEY ( \fIcolumn\fR [, \&.\&.\&. ] ) REFERENCES \fIreftable\fR [ ( \fIrefcolumn\fR [, \&.\&.\&. ] ) ] [ MATCH \fImatchtype\fR ] [ ON DELETE \fIaction\fR ] [ ON UPDATE \fIaction\fR ] (table constraint)
346
These clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table\&. If
348
is omitted, the primary key of the
350
is used\&. The referenced columns must be the columns of a non\-deferrable unique or primary key constraint in the referenced table\&. Note that foreign key constraints cannot be defined between temporary tables and permanent tables\&.
352
A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type\&. There are three match types:
355
MATCH SIMPLE, which is also the default\&.
357
will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null\&.
359
allows some foreign key columns to be null while other parts of the foreign key are not null\&.
361
is not yet implemented\&.
363
In addition, when the data in the referenced columns is changed, certain actions are performed on the data in this table\(aqs columns\&. The
365
clause specifies the action to perform when a referenced row in the referenced table is being deleted\&. Likewise, the
367
clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value\&. If the row is updated, but the referenced column is not actually changed, no action is done\&. Referential actions other than the
369
check cannot be deferred, even if the constraint is declared deferrable\&. There are the following possible actions for each clause:
373
Produce an error indicating that the deletion or update would create a foreign key constraint violation\&. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows\&. This is the default action\&.
378
Produce an error indicating that the deletion or update would create a foreign key constraint violation\&. This is the same as
380
except that the check is not deferrable\&.
385
Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively\&.
390
Set the referencing column(s) to null\&.
395
Set the referencing column(s) to their default values\&.
398
If the referenced column(s) are changed frequently, it might be wise to add an index to the foreign key column so that referential actions associated with the foreign key column can be performed more efficiently\&.
401
DEFERRABLE, NOT DEFERRABLE
403
This controls whether the constraint can be deferred\&. A constraint that is not deferrable will be checked immediately after every command\&. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the
404
SET CONSTRAINTS (\fBSET_CONSTRAINTS\fR(7))
407
is the default\&. Currently, only
412
(foreign key) constraints accept this clause\&.
416
constraints are not deferrable\&.
419
INITIALLY IMMEDIATE, INITIALLY DEFERRED
421
If a constraint is deferrable, this clause specifies the default time to check the constraint\&. If the constraint is
422
INITIALLY IMMEDIATE, it is checked after each statement\&. This is the default\&. If the constraint is
423
INITIALLY DEFERRED, it is checked only at the end of the transaction\&. The constraint check time can be altered with the
424
SET CONSTRAINTS (\fBSET_CONSTRAINTS\fR(7))
428
WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] )
430
This clause specifies optional storage parameters for a table or index; see
432
for more information\&. The
434
clause for a table can also include
437
OIDS) to specify that rows of the new table should have OIDs (object identifiers) assigned to them, or
439
to specify that the rows should not have OIDs\&. If
441
is not specified, the default setting depends upon the
443
configuration parameter\&. (If the new table inherits from any tables that have OIDs, then
445
is forced even if the command says
450
is specified or implied, the new table does not store OIDs and no OID will be assigned for a row inserted into it\&. This is generally considered worthwhile, since it will reduce OID consumption and thereby postpone the wraparound of the 32\-bit OID counter\&. Once the counter wraps around, OIDs can no longer be assumed to be unique, which makes them considerably less useful\&. In addition, excluding OIDs from a table reduces the space required to store the table on disk by 4 bytes per row (on most machines), slightly improving performance\&.
452
To remove OIDs from a table after it has been created, use
453
ALTER TABLE (\fBALTER_TABLE\fR(7))\&.
456
WITH OIDS, WITHOUT OIDS
458
These are obsolescent syntaxes equivalent to
461
WITH (OIDS=FALSE), respectively\&. If you wish to give both an
463
setting and storage parameters, you must use the
470
The behavior of temporary tables at the end of a transaction block can be controlled using
471
ON COMMIT\&. The three options are:
475
No special action is taken at the ends of transactions\&. This is the default behavior\&.
480
All rows in the temporary table will be deleted at the end of each transaction block\&. Essentially, an automatic
482
is done at each commit\&.
487
The temporary table will be dropped at the end of the current transaction block\&.
491
TABLESPACE \fItablespace\fR
495
is the name of the tablespace in which the new table is to be created\&. If not specified,
499
if the table is temporary\&.
502
USING INDEX TABLESPACE \fItablespace\fR
504
This clause allows selection of the tablespace in which the index associated with a
508
constraint will be created\&. If not specified,
512
if the table is temporary\&.
514
.SS "Storage Parameters"
515
.\" storage parameters
521
for tables, and for indexes associated with a
525
constraint\&. Storage parameters for indexes are documented in
526
CREATE INDEX (\fBCREATE_INDEX\fR(7))\&. The storage parameters currently available for tables are listed below\&. For each parameter, unless noted, there is an additional parameter with the same name prefixed with
527
toast\&., which can be used to control the behavior of the table\(aqs secondary
530
Section 55.2, \(lqTOAST\(rq, in the documentation
531
for more information about TOAST)\&. Note that the TOAST table inherits the
533
values from its parent table, if there are no
539
The fillfactor for a table is a percentage between 10 and 100\&. 100 (complete packing) is the default\&. When a smaller fillfactor is specified,
541
operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page\&. This gives
543
a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page\&. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate\&. This parameter cannot be set for TOAST tables\&.
546
autovacuum_enabled, toast\&.autovacuum_enabled (boolean)
548
Enables or disables the autovacuum daemon on a particular table\&. If true, the autovacuum daemon will initiate a
550
operation on a particular table when the number of updated or deleted tuples exceeds
551
autovacuum_vacuum_threshold
553
autovacuum_vacuum_scale_factor
554
times the number of live tuples currently estimated to be in the relation\&. Similarly, it will initiate an
556
operation when the number of inserted, updated or deleted tuples exceeds
557
autovacuum_analyze_threshold
559
autovacuum_analyze_scale_factor
560
times the number of live tuples currently estimated to be in the relation\&. If false, this table will not be autovacuumed, except to prevent transaction Id wraparound\&. See
561
Section 23.1.4, \(lqPreventing Transaction ID Wraparound Failures\(rq, in the documentation
562
for more about wraparound prevention\&. Observe that this variable inherits its value from the
567
autovacuum_vacuum_threshold, toast\&.autovacuum_vacuum_threshold (integer)
569
Minimum number of updated or deleted tuples before initiate a
571
operation on a particular table\&.
574
autovacuum_vacuum_scale_factor, toast\&.autovacuum_vacuum_scale_factor (float4)
579
autovacuum_vacuum_threshold\&.
582
autovacuum_analyze_threshold (integer)
584
Minimum number of inserted, updated, or deleted tuples before initiate an
586
operation on a particular table\&.
589
autovacuum_analyze_scale_factor (float4)
594
autovacuum_analyze_threshold\&.
597
autovacuum_vacuum_cost_delay, toast\&.autovacuum_vacuum_cost_delay (integer)
600
autovacuum_vacuum_cost_delay
604
autovacuum_vacuum_cost_limit, toast\&.autovacuum_vacuum_cost_limit (integer)
607
autovacuum_vacuum_cost_limit
611
autovacuum_freeze_min_age, toast\&.autovacuum_freeze_min_age (integer)
614
vacuum_freeze_min_age
615
parameter\&. Note that autovacuum will ignore attempts to set a per\-table
616
autovacuum_freeze_min_age
617
larger than the half system\-wide
618
autovacuum_freeze_max_age
622
autovacuum_freeze_max_age, toast\&.autovacuum_freeze_max_age (integer)
625
autovacuum_freeze_max_age
626
parameter\&. Note that autovacuum will ignore attempts to set a per\-table
627
autovacuum_freeze_max_age
628
larger than the system\-wide setting (it can only be set smaller)\&. Note that while you can set
629
autovacuum_freeze_max_age
630
very small, or even zero, this is usually unwise since it will force frequent vacuuming\&.
633
autovacuum_freeze_table_age, toast\&.autovacuum_freeze_table_age (integer)
636
vacuum_freeze_table_age
641
Using OIDs in new applications is not recommended: where possible, using a
643
or other sequence generator as the table\(aqs primary key is preferred\&. However, if your application does make use of OIDs to identify specific rows of a table, it is recommended to create a unique constraint on the
645
column of that table, to ensure that OIDs in the table will indeed uniquely identify rows even after counter wraparound\&. Avoid assuming that OIDs are unique across tables; if you need a database\-wide unique identifier, use the combination of
647
and row OID for the purpose\&.
653
.nr an-no-space-flag 1
663
is not recommended for tables with no primary key, since without either an OID or a unique data key, it is difficult to identify specific rows\&.
668
automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness\&. Thus, it is not necessary to create an index explicitly for primary key columns\&. (See
669
CREATE INDEX (\fBCREATE_INDEX\fR(7))
670
for more information\&.)
672
Unique constraints and primary keys are not inherited in the current implementation\&. This makes the combination of inheritance and unique constraints rather dysfunctional\&.
674
A table cannot have more than 1600 columns\&. (In practice, the effective limit is usually lower because of tuple\-length constraints\&.)
687
code char(5) CONSTRAINT firstkey PRIMARY KEY,
688
title varchar(40) NOT NULL,
689
did integer NOT NULL,
692
len interval hour to minute
703
CREATE TABLE distributors (
704
did integer PRIMARY KEY DEFAULT nextval(\(aqserial\(aq),
705
name varchar(40) NOT NULL CHECK (name <> \(aq\(aq)
712
Create a table with a 2\-dimensional array:
718
CREATE TABLE array_int (
726
Define a unique table constraint for the table
727
films\&. Unique table constraints can be defined on one or more columns of the table:
739
len interval hour to minute,
740
CONSTRAINT production UNIQUE(date_prod)
747
Define a check column constraint:
753
CREATE TABLE distributors (
754
did integer CHECK (did > 100),
762
Define a check table constraint:
768
CREATE TABLE distributors (
771
CONSTRAINT con1 CHECK (did > 100 AND name <> \(aq\(aq)
778
Define a primary key table constraint for the table
791
len interval hour to minute,
792
CONSTRAINT code_title PRIMARY KEY(code,title)
799
Define a primary key constraint for table
800
distributors\&. The following two examples are equivalent, the first using the table constraint syntax, the second the column constraint syntax:
806
CREATE TABLE distributors (
820
CREATE TABLE distributors (
821
did integer PRIMARY KEY,
829
Assign a literal constant default value for the column
830
name, arrange for the default value of column
832
to be generated by selecting the next value of a sequence object, and make the default value of
834
be the time at which the row is inserted:
840
CREATE TABLE distributors (
841
name varchar(40) DEFAULT \(aqLuso Films\(aq,
842
did integer DEFAULT nextval(\(aqdistributors_serial\(aq),
843
modtime timestamp DEFAULT current_timestamp
852
column constraints on the table
853
distributors, one of which is explicitly given a name:
859
CREATE TABLE distributors (
860
did integer CONSTRAINT no_null NOT NULL,
861
name varchar(40) NOT NULL
868
Define a unique constraint for the
876
CREATE TABLE distributors (
878
name varchar(40) UNIQUE
885
The same, specified as a table constraint:
891
CREATE TABLE distributors (
901
Create the same table, specifying 70% fill factor for both the table and its unique index:
907
CREATE TABLE distributors (
910
UNIQUE(name) WITH (fillfactor=70)
912
WITH (fillfactor=70);
920
with an exclusion constraint that prevents any two circles from overlapping:
926
CREATE TABLE circles (
928
EXCLUDE USING gist (c WITH &&)
944
CREATE TABLE cinemas (
948
) TABLESPACE diskvol1;
954
Create a composite type and a typed table:
960
CREATE TYPE employee_type AS (name text, salary numeric);
962
CREATE TABLE employees OF employee_type (
964
salary WITH OPTIONS DEFAULT 1000
974
command conforms to the
976
standard, with exceptions listed below\&.
977
.SS "Temporary Tables"
979
Although the syntax of
980
CREATE TEMPORARY TABLE
981
resembles that of the SQL standard, the effect is not the same\&. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them\&.
983
instead requires each session to issue its own
984
CREATE TEMPORARY TABLE
985
command for each temporary table to be used\&. This allows different sessions to use the same temporary table name for different purposes, whereas the standard\(aqs approach constrains all instances of a given temporary table name to have the same table structure\&.
987
The standard\(aqs definition of the behavior of temporary tables is widely ignored\&.
988
PostgreSQL\(aqs behavior on this point is similar to that of several other SQL databases\&.
990
The standard\(aqs distinction between global and local temporary tables is not in
991
PostgreSQL, since that distinction depends on the concept of modules, which
993
does not have\&. For compatibility\(aqs sake,
999
keywords in a temporary table declaration, but they have no effect\&.
1003
clause for temporary tables also resembles the SQL standard, but has some differences\&. If the
1005
clause is omitted, SQL specifies that the default behavior is
1006
ON COMMIT DELETE ROWS\&. However, the default behavior in
1009
ON COMMIT PRESERVE ROWS\&. The
1011
option does not exist in SQL\&.
1012
.SS "Non\-deferred Uniqueness Constraints"
1018
constraint is not deferrable,
1020
checks for uniqueness immediately whenever a row is inserted or modified\&. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values\&. To obtain standard\-compliant behavior, declare the constraint as
1022
but not deferred (i\&.e\&.,
1023
INITIALLY IMMEDIATE)\&. Be aware that this can be significantly slower than immediate uniqueness checking\&.
1024
.SS "Column Check Constraints"
1026
The SQL standard says that
1028
column constraints can only refer to the column they apply to; only
1030
table constraints can refer to multiple columns\&.
1032
does not enforce this restriction; it treats column and table check constraints alike\&.
1033
.SS "EXCLUDE Constraint"
1037
constraint type is a
1040
.SS "NULL \(lqConstraint\(rq"
1043
NULL\(lqconstraint\(rq
1044
(actually a non\-constraint) is a
1046
extension to the SQL standard that is included for compatibility with some other database systems (and for symmetry with the
1048
constraint)\&. Since it is the default for any column, its presence is simply noise\&.
1051
Multiple inheritance via the
1055
language extension\&. SQL:1999 and later define single inheritance using a different syntax and different semantics\&. SQL:1999\-style inheritance is not yet supported by
1057
.SS "Zero\-column Tables"
1060
allows a table of no columns to be created (for example,
1061
CREATE TABLE foo();)\&. This is an extension from the SQL standard, which does not allow zero\-column tables\&. Zero\-column tables are not in themselves very useful, but disallowing them creates odd special cases for
1062
ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec restriction\&.
1069
extension; neither storage parameters nor OIDs are in the standard\&.
1074
concept of tablespaces is not part of the standard\&. Hence, the clauses
1077
USING INDEX TABLESPACE
1081
Typed tables implement a subset of the SQL standard\&. According to the standard, a typed table has columns corresponding to the underlying composite type as well as one other column that is the
1082
\(lqself\-referencing column\(rq\&. PostgreSQL does not support these self\-referencing columns explicitly, but the same effect can be had using the OID feature\&.
1084
ALTER TABLE (\fBALTER_TABLE\fR(7)), DROP TABLE (\fBDROP_TABLE\fR(7)), CREATE TABLESPACE (\fBCREATE_TABLESPACE\fR(7)), CREATE TYPE (\fBCREATE_TYPE\fR(7))
added
removed
doc/src/sgml/man7/CREATE_TABLE.7
