1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
16
HREF="sql-commands.html"><LINK
19
HREF="sql-createserver.html"><LINK
21
TITLE="CREATE TABLE AS"
22
HREF="sql-createtableas.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
57
HREF="sql-createserver.html"
67
HREF="sql-createserver.html"
80
TITLE="CREATE TABLE AS"
81
HREF="sql-createtableas.html"
89
TITLE="CREATE TABLE AS"
90
HREF="sql-createtableas.html"
101
NAME="SQL-CREATETABLE"
111
>CREATE TABLE -- define a new table</DIV
113
CLASS="REFSYNOPSISDIV"
121
>CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <TT
145
>column_constraint</I
176
>storage_parameter</I
183
>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
184
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
192
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <TT
212
>column_constraint</I
226
>storage_parameter</I
233
>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
234
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
249
>column_constraint</I
297
> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
309
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
389
> [, ... ] ) REFERENCES <TT
400
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <TT
411
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
426
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
446
> constraints are:</SPAN
453
>storage_parameter</I
461
[ USING INDEX TABLESPACE <TT
480
> constraint is:</SPAN
499
> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]</PRE
504
NAME="SQL-CREATETABLE-DESCRIPTION"
512
> will create a new, initially empty table
513
in the current database. The table will be owned by the user issuing the
517
> If a schema name is given (for example, <TT
520
myschema.mytable ...</TT
521
>) then the table is created in the specified
522
schema. Otherwise it is created in the current schema. Temporary
523
tables exist in a special schema, so a schema name cannot be given
524
when creating a temporary table. The name of the table must be
525
distinct from the name of any other table, sequence, index, view,
526
or foreign table in the same schema.
532
> also automatically creates a data
533
type that represents the composite type corresponding
534
to one row of the table. Therefore, tables cannot have the same
535
name as any existing data type in the same schema.
538
> The optional constraint clauses specify constraints (tests) that
539
new or updated rows must satisfy for an insert or update operation
540
to succeed. A constraint is an SQL object that helps define the
541
set of valid values in the table in various ways.
544
> There are two ways to define constraints: table constraints and
545
column constraints. A column constraint is defined as part of a
546
column definition. A table constraint definition is not tied to a
547
particular column, and it can encompass more than one column.
548
Every column constraint can also be written as a table constraint;
549
a column constraint is only a notational convenience for use when the
550
constraint only affects one column.
575
> If specified, the table is created as a temporary table.
576
Temporary tables are automatically dropped at the end of a
577
session, or optionally at the end of the current transaction
581
> below). Existing permanent
582
tables with the same name are not visible to the current session
583
while the temporary table exists, unless they are referenced
584
with schema-qualified names. Any indexes created on a temporary
585
table are automatically temporary as well.
589
HREF="routine-vacuuming.html#AUTOVACUUM"
590
>autovacuum daemon</A
592
access and therefore cannot vacuum or analyze temporary tables.
593
For this reason, appropriate vacuum and analyze operations should be
594
performed via session SQL commands. For example, if a temporary
595
table is going to be used in complex queries, it is wise to run
599
> on the temporary table after it is populated.
609
can be written before <TT
616
This makes no difference in <SPAN
621
HREF="sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY"
635
> If specified, the table is created as an unlogged table. Data written
636
to unlogged tables is not written to the write-ahead log (see <A
639
>), which makes them considerably faster than ordinary
640
tables. However, they are not crash-safe: an unlogged table is
641
automatically truncated after a crash or unclean shutdown. The contents
642
of an unlogged table are also not replicated to standby servers.
643
Any indexes created on an unlogged table are automatically unlogged as
644
well; however, unlogged <A
648
currently not supported and cannot be created on an unlogged table.
658
> Do not throw an error if a relation with the same name already exists.
659
A notice is issued in this case. Note that there is no guarantee that
660
the existing relation is anything like the one that would have been
673
> The name (optionally schema-qualified) of the table to be created.
692
structure from the specified composite type (name optionally
693
schema-qualified). A typed table is tied to its type; for
694
example the table will be dropped if the type is dropped
697
>DROP TYPE ... CASCADE</TT
701
> When a typed table is created, then the data types of the
702
columns are determined by the underlying composite type and are
703
not specified by the <TT
710
> command can add defaults
711
and constraints to the table and can specify storage parameters.
723
> The name of a column to be created in the new table.
735
> The data type of the column. This can include array
736
specifiers. For more information on the data types supported by
761
> clause assigns a collation to
762
the column (which must be of a collatable data type).
763
If not specified, the column data type's default collation is used.
781
> clause specifies a list of
782
tables from which the new table automatically inherits all
789
> creates a persistent relationship
790
between the new child table and its parent table(s). Schema
791
modifications to the parent(s) normally propagate to children
792
as well, and by default the data of the child table is included in
793
scans of the parent(s).
796
> If the same column name exists in more than one parent
797
table, an error is reported unless the data types of the columns
798
match in each of the parent tables. If there is no conflict,
799
then the duplicate columns are merged to form a single column in
800
the new table. If the column name list of the new table
801
contains a column name that is also inherited, the data type must
802
likewise match the inherited column(s), and the column
803
definitions are merged into one. If the
804
new table explicitly specifies a default value for the column,
805
this default overrides any defaults from inherited declarations
806
of the column. Otherwise, any parents that specify default
807
values for the column must all specify the same default, or an
808
error will be reported.
814
> constraints are merged in essentially the same way as
815
columns: if multiple parent tables and/or the new table definition
816
contain identically-named <TT
820
constraints must all have the same check expression, or an error will be
821
reported. Constraints having the same name and expression will
822
be merged into one copy. Notice that an unnamed <TT
826
constraint in the new table will never be merged, since a unique name
827
will always be chosen for it.
833
> settings are also copied from parent tables.
856
> clause specifies a table from which
857
the new table automatically copies all column names, their data types,
858
and their not-null constraints.
864
>, the new table and original table
865
are completely decoupled after creation is complete. Changes to the
866
original table will not be applied to the new table, and it is not
867
possible to include data of the new table in scans of the original
871
> Default expressions for the copied column definitions will only be
874
>INCLUDING DEFAULTS</TT
876
default behavior is to exclude default expressions, resulting in the
877
copied columns in the new table having null defaults.
880
> Not-null constraints are always copied to the new table.
884
> constraints will only be copied if
887
>INCLUDING CONSTRAINTS</TT
888
> is specified; other types of
889
constraints will never be copied. Also, no distinction is made between
890
column constraints and table constraints — when constraints are
891
requested, all check constraints are copied.
894
> Any indexes on the original table will not be created on the new
895
table, unless the <TT
897
>INCLUDING INDEXES</TT
905
> settings for the copied column definitions will only
908
>INCLUDING STORAGE</TT
910
default behavior is to exclude <TT
913
> settings, resulting
914
in the copied columns in the new table having type-specific default
915
settings. For more on <TT
920
HREF="storage-toast.html"
925
> Comments for the copied columns, constraints, and indexes
926
will only be copied if <TT
928
>INCLUDING COMMENTS</TT
930
is specified. The default behavior is to exclude comments, resulting in
931
the copied columns and constraints in the new table having no comments.
937
> is an abbreviated form of
940
>INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</TT
944
> Note also that unlike <TT
948
constraints copied by <TT
951
> are not merged with similarly
952
named columns and constraints.
953
If the same name is specified explicitly or in another
957
> clause, an error is signalled.
972
> An optional name for a column or table constraint. If the
973
constraint is violated, the constraint name is present in error messages,
974
so constraint names like <TT
976
>col must be positive</TT
978
to communicate helpful constraint information to client applications.
979
(Double-quotes are needed to specify constraint names that contain spaces.)
980
If a constraint name is not specified, the system generates a name.
990
> The column is not allowed to contain null values.
1000
> The column is allowed to contain null values. This is the default.
1003
> This clause is only provided for compatibility with
1004
non-standard SQL databases. Its use is discouraged in new
1023
> clause specifies an expression producing a
1024
Boolean result which new or updated rows must satisfy for an
1025
insert or update operation to succeed. Expressions evaluating
1026
to TRUE or UNKNOWN succeed. Should any row of an insert or
1027
update operation produce a FALSE result an error exception is
1028
raised and the insert or update does not alter the database. A
1029
check constraint specified as a column constraint should
1030
reference that column's value only, while an expression
1031
appearing in a table constraint can reference multiple columns.
1037
> expressions cannot contain
1038
subqueries nor refer to variables other than columns of the
1058
> clause assigns a default data value for
1059
the column whose column definition it appears within. The value
1060
is any variable-free expression (subqueries and cross-references
1061
to other columns in the current table are not allowed). The
1062
data type of the default expression must match the data type of the
1066
> The default expression will be used in any insert operation that
1067
does not specify a value for the column. If there is no default
1068
for a column, then the default is null.
1075
> (column constraint)<BR><TT
1083
> (table constraint)</DT
1089
> constraint specifies that a
1090
group of one or more columns of a table can contain
1091
only unique values. The behavior of the unique table constraint
1092
is the same as that for column constraints, with the additional
1093
capability to span multiple columns.
1096
> For the purpose of a unique constraint, null values are not
1100
> Each unique table constraint must name a set of columns that is
1101
different from the set of columns named by any other unique or
1102
primary key constraint defined for the table. (Otherwise it
1103
would just be the same constraint listed twice.)
1110
> (column constraint)<BR><TT
1118
> (table constraint)</DT
1121
> The primary key constraint specifies that a column or columns of a table
1122
can contain only unique (non-duplicate), nonnull values.
1134
identifying a set of columns as primary key also provides
1135
metadata about the design of the schema, as a primary key
1136
implies that other tables
1137
can rely on this set of columns as a unique identifier for rows.
1140
> Only one primary key can be specified for a table, whether as a
1141
column constraint or a table constraint.
1144
> The primary key constraint should name a set of columns that is
1145
different from other sets of columns named by any unique
1146
constraint defined for the same table.
1151
NAME="SQL-CREATETABLE-EXCLUDE"
1155
>EXCLUDE [ USING <TT
1173
>index_parameters</I
1187
> clause defines an exclusion
1188
constraint, which guarantees that if
1189
any two rows are compared on the specified column(s) or
1190
expression(s) using the specified operator(s), not all of these
1191
comparisons will return <TT
1195
specified operators test for equality, this is equivalent to a
1199
> constraint, although an ordinary unique constraint
1200
will be faster. However, exclusion constraints can specify
1201
constraints that are more general than simple equality.
1202
For example, you can specify a constraint that
1203
no two rows in the table contain overlapping circles
1205
HREF="datatype-geometric.html"
1214
> Exclusion constraints are implemented using
1215
an index, so each specified operator must be associated with an
1216
appropriate operator class
1218
HREF="indexes-opclass.html"
1220
>) for the index access
1227
The operators are required to be commutative.
1234
can optionally specify an operator class and/or ordering options;
1235
these are described fully under
1237
HREF="sql-createindex.html"
1242
> The access method must support <TT
1248
>); at present this means <ACRONYM
1252
cannot be used. Although it's allowed, there is little point in using
1253
B-tree or hash indexes with an exclusion constraint, because this
1254
does nothing that an ordinary unique constraint doesn't do better.
1255
So in practice the access method will always be <ACRONYM
1266
> allows you to specify an
1267
exclusion constraint on a subset of the table; internally this creates a
1268
partial index. Note that parentheses are required around the predicate.
1300
> (column constraint)<BR><TT
1338
(table constraint)</DT
1341
> These clauses specify a foreign key constraint, which requires
1342
that a group of one or more columns of the new table must only
1343
contain values that match values in the referenced
1344
column(s) of some row of the referenced table. If <TT
1350
primary key of the <TT
1356
is used. The referenced columns must be the columns of a non-deferrable
1357
unique or primary key constraint in the referenced table. Note that
1358
foreign key constraints cannot be defined between temporary tables and
1362
> A value inserted into the referencing column(s) is matched against the
1363
values of the referenced table and referenced columns using the
1364
given match type. There are three match types: <TT
1375
>, which is also the default. <TT
1379
> will not allow one column of a multicolumn foreign key
1380
to be null unless all foreign key columns are null.
1384
> allows some foreign key columns
1385
to be null while other parts of the foreign key are not
1389
> is not yet implemented.
1392
> In addition, when the data in the referenced columns is changed,
1393
certain actions are performed on the data in this table's
1397
> clause specifies the
1398
action to perform when a referenced row in the referenced table is
1399
being deleted. Likewise, the <TT
1403
clause specifies the action to perform when a referenced column
1404
in the referenced table is being updated to a new value. If the
1405
row is updated, but the referenced column is not actually
1406
changed, no action is done. Referential actions other than the
1410
> check cannot be deferred, even if
1411
the constraint is declared deferrable. There are the following possible
1412
actions for each clause:
1417
CLASS="VARIABLELIST"
1426
> Produce an error indicating that the deletion or update
1427
would create a foreign key constraint violation.
1428
If the constraint is deferred, this
1429
error will be produced at constraint check time if there still
1430
exist any referencing rows. This is the default action.
1440
> Produce an error indicating that the deletion or update
1441
would create a foreign key constraint violation.
1442
This is the same as <TT
1446
the check is not deferrable.
1456
> Delete any rows referencing the deleted row, or update the
1457
value of the referencing column to the new value of the
1458
referenced column, respectively.
1468
> Set the referencing column(s) to null.
1478
> Set the referencing column(s) to their default values.
1486
> If the referenced column(s) are changed frequently, it might be wise to
1487
add an index to the foreign key column so that referential actions
1488
associated with the foreign key column can be performed more
1502
> This controls whether the constraint can be deferred. A
1503
constraint that is not deferrable will be checked immediately
1504
after every command. Checking of constraints that are
1505
deferrable can be postponed until the end of the transaction
1507
HREF="sql-set-constraints.html"
1528
> (foreign key) constraints accept this
1535
> constraints are not
1542
>INITIALLY IMMEDIATE</TT
1545
>INITIALLY DEFERRED</TT
1549
> If a constraint is deferrable, this clause specifies the default
1550
time to check the constraint. If the constraint is
1553
>INITIALLY IMMEDIATE</TT
1554
>, it is checked after each
1555
statement. This is the default. If the constraint is
1558
>INITIALLY DEFERRED</TT
1559
>, it is checked only at the
1560
end of the transaction. The constraint check time can be
1562
HREF="sql-set-constraints.html"
1573
>storage_parameter</I
1584
> This clause specifies optional storage parameters for a table or index;
1586
HREF="sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS"
1588
>Storage Parameters</I
1591
information. The <TT
1595
table can also include <TT
1602
to specify that rows of the new table
1603
should have OIDs (object identifiers) assigned to them, or
1607
> to specify that the rows should not have OIDs.
1611
> is not specified, the default setting depends upon
1613
HREF="runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS"
1614
>default_with_oids</A
1615
> configuration parameter.
1616
(If the new table inherits from any tables that have OIDs, then
1620
> is forced even if the command says
1630
> is specified or implied, the new
1631
table does not store OIDs and no OID will be assigned for a row inserted
1632
into it. This is generally considered worthwhile, since it
1633
will reduce OID consumption and thereby postpone the wraparound
1634
of the 32-bit OID counter. Once the counter wraps around, OIDs
1635
can no longer be assumed to be unique, which makes them
1636
considerably less useful. In addition, excluding OIDs from a
1637
table reduces the space required to store the table on disk by
1638
4 bytes per row (on most machines), slightly improving performance.
1641
> To remove OIDs from a table after it has been created, use <A
1642
HREF="sql-altertable.html"
1657
> These are obsolescent syntaxes equivalent to <TT
1663
>WITH (OIDS=FALSE)</TT
1664
>, respectively. If you wish to give
1668
> setting and storage parameters, you must use
1672
> syntax; see above.
1682
> The behavior of temporary tables at the end of a transaction
1683
block can be controlled using <TT
1687
The three options are:
1692
CLASS="VARIABLELIST"
1701
> No special action is taken at the ends of transactions.
1702
This is the default behavior.
1712
> All rows in the temporary table will be deleted at the end
1713
of each transaction block. Essentially, an automatic <A
1714
HREF="sql-truncate.html"
1727
> The temporary table will be dropped at the end of the current
1754
of the tablespace in which the new table is to be created.
1757
HREF="runtime-config-client.html#GUC-DEFAULT-TABLESPACE"
1758
>default_tablespace</A
1761
HREF="runtime-config-client.html#GUC-TEMP-TABLESPACES"
1762
>temp_tablespaces</A
1763
> if the table is temporary.
1769
>USING INDEX TABLESPACE <TT
1778
> This clause allows selection of the tablespace in which the index
1779
associated with a <TT
1789
> constraint will be created.
1792
HREF="runtime-config-client.html#GUC-DEFAULT-TABLESPACE"
1793
>default_tablespace</A
1796
HREF="runtime-config-client.html#GUC-TEMP-TABLESPACES"
1797
>temp_tablespaces</A
1798
> if the table is temporary.
1806
NAME="SQL-CREATETABLE-STORAGE-PARAMETERS"
1809
>Storage Parameters</H3
1814
> clause can specify <I
1816
>storage parameters</I
1818
for tables, and for indexes associated with a <TT
1829
Storage parameters for
1830
indexes are documented in <A
1831
HREF="sql-createindex.html"
1834
The storage parameters currently
1835
available for tables are listed below. For each parameter, unless noted,
1836
there is an additional parameter with the same name prefixed with
1840
>, which can be used to control the behavior of the
1841
table's secondary <ACRONYM
1846
HREF="storage-toast.html"
1848
> for more information about TOAST).
1849
Note that the TOAST table inherits the
1853
> values from its parent table, if there are
1856
>toast.autovacuum_*</TT
1862
CLASS="VARIABLELIST"
1874
> The fillfactor for a table is a percentage between 10 and 100.
1875
100 (complete packing) is the default. When a smaller fillfactor
1879
> operations pack table pages only
1880
to the indicated percentage; the remaining space on each page is
1881
reserved for updating rows on that page. This gives <TT
1885
a chance to place the updated copy of a row on the same page as the
1886
original, which is more efficient than placing it on a different page.
1887
For a table whose entries are never updated, complete packing is the
1888
best choice, but in heavily updated tables smaller fillfactors are
1889
appropriate. This parameter cannot be set for TOAST tables.
1895
>autovacuum_enabled</TT
1898
>toast.autovacuum_enabled</TT
1905
> Enables or disables the autovacuum daemon on a particular table.
1906
If true, the autovacuum daemon will initiate a <TT
1910
on a particular table when the number of updated or deleted tuples exceeds
1913
>autovacuum_vacuum_threshold</TT
1917
>autovacuum_vacuum_scale_factor</TT
1918
> times the number of live tuples
1919
currently estimated to be in the relation.
1920
Similarly, it will initiate an <TT
1923
> operation when the
1924
number of inserted, updated or deleted tuples exceeds
1927
>autovacuum_analyze_threshold</TT
1931
>autovacuum_analyze_scale_factor</TT
1932
> times the number of live tuples
1933
currently estimated to be in the relation.
1934
If false, this table will not be autovacuumed, except to prevent
1935
transaction Id wraparound. See <A
1936
HREF="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND"
1939
more about wraparound prevention.
1940
Observe that this variable inherits its value from the <A
1941
HREF="runtime-config-autovacuum.html#GUC-AUTOVACUUM"
1949
>autovacuum_vacuum_threshold</TT
1952
>toast.autovacuum_vacuum_threshold</TT
1959
> Minimum number of updated or deleted tuples before initiate a
1963
> operation on a particular table.
1969
>autovacuum_vacuum_scale_factor</TT
1972
>toast.autovacuum_vacuum_scale_factor</TT
1979
> Multiplier for <TT
1985
>autovacuum_vacuum_threshold</TT
1992
>autovacuum_analyze_threshold</TT
1999
> Minimum number of inserted, updated, or deleted tuples before initiate an
2003
> operation on a particular table.
2009
>autovacuum_analyze_scale_factor</TT
2016
> Multiplier for <TT
2022
>autovacuum_analyze_threshold</TT
2029
>autovacuum_vacuum_cost_delay</TT
2032
>toast.autovacuum_vacuum_cost_delay</TT
2040
HREF="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY"
2041
>autovacuum_vacuum_cost_delay</A
2048
>autovacuum_vacuum_cost_limit</TT
2051
>toast.autovacuum_vacuum_cost_limit</TT
2059
HREF="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT"
2060
>autovacuum_vacuum_cost_limit</A
2067
>autovacuum_freeze_min_age</TT
2070
>toast.autovacuum_freeze_min_age</TT
2078
HREF="runtime-config-client.html#GUC-VACUUM-FREEZE-MIN-AGE"
2079
>vacuum_freeze_min_age</A
2080
> parameter. Note that
2081
autovacuum will ignore attempts to set a per-table
2084
>autovacuum_freeze_min_age</TT
2085
> larger than the half system-wide
2087
HREF="runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE"
2088
>autovacuum_freeze_max_age</A
2095
>autovacuum_freeze_max_age</TT
2098
>toast.autovacuum_freeze_max_age</TT
2106
HREF="runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE"
2107
>autovacuum_freeze_max_age</A
2108
> parameter. Note that
2109
autovacuum will ignore attempts to set a per-table
2112
>autovacuum_freeze_max_age</TT
2113
> larger than the system-wide setting
2114
(it can only be set smaller). Note that while you can set
2117
>autovacuum_freeze_max_age</TT
2118
> very small, or even zero, this is
2119
usually unwise since it will force frequent vacuuming.
2125
>autovacuum_freeze_table_age</TT
2128
>toast.autovacuum_freeze_table_age</TT
2136
HREF="runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE"
2137
>vacuum_freeze_table_age</A
2148
NAME="SQL-CREATETABLE-NOTES"
2153
> Using OIDs in new applications is not recommended: where
2154
possible, using a <TT
2158
generator as the table's primary key is preferred. However, if
2159
your application does make use of OIDs to identify specific
2160
rows of a table, it is recommended to create a unique constraint
2164
> column of that table, to ensure that
2165
OIDs in the table will indeed uniquely identify rows even after
2166
counter wraparound. Avoid assuming that OIDs are unique across
2167
tables; if you need a database-wide unique identifier, use the
2171
> and row OID for the
2184
> is not recommended
2185
for tables with no primary key, since without either an OID or a
2186
unique data key, it is difficult to identify specific rows.
2194
> automatically creates an
2195
index for each unique constraint and primary key constraint to
2196
enforce uniqueness. Thus, it is not necessary to create an
2197
index explicitly for primary key columns. (See <A
2198
HREF="sql-createindex.html"
2200
> for more information.)
2203
> Unique constraints and primary keys are not inherited in the
2204
current implementation. This makes the combination of
2205
inheritance and unique constraints rather dysfunctional.
2208
> A table cannot have more than 1600 columns. (In practice, the
2209
effective limit is usually lower because of tuple-length constraints.)
2215
NAME="SQL-CREATETABLE-EXAMPLES"
2230
CLASS="PROGRAMLISTING"
2231
>CREATE TABLE films (
2232
code char(5) CONSTRAINT firstkey PRIMARY KEY,
2233
title varchar(40) NOT NULL,
2234
did integer NOT NULL,
2237
len interval hour to minute
2242
CLASS="PROGRAMLISTING"
2243
>CREATE TABLE distributors (
2244
did integer PRIMARY KEY DEFAULT nextval('serial'),
2245
name varchar(40) NOT NULL CHECK (name <> '')
2250
> Create a table with a 2-dimensional array:
2253
CLASS="PROGRAMLISTING"
2254
>CREATE TABLE array_int (
2260
> Define a unique table constraint for the table
2264
>. Unique table constraints can be defined
2265
on one or more columns of the table:
2268
CLASS="PROGRAMLISTING"
2269
>CREATE TABLE films (
2275
len interval hour to minute,
2276
CONSTRAINT production UNIQUE(date_prod)
2281
> Define a check column constraint:
2284
CLASS="PROGRAMLISTING"
2285
>CREATE TABLE distributors (
2286
did integer CHECK (did > 100),
2292
> Define a check table constraint:
2295
CLASS="PROGRAMLISTING"
2296
>CREATE TABLE distributors (
2299
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
2304
> Define a primary key table constraint for the table
2311
CLASS="PROGRAMLISTING"
2312
>CREATE TABLE films (
2318
len interval hour to minute,
2319
CONSTRAINT code_title PRIMARY KEY(code,title)
2324
> Define a primary key constraint for table
2328
>. The following two examples are
2329
equivalent, the first using the table constraint syntax, the second
2330
the column constraint syntax:
2333
CLASS="PROGRAMLISTING"
2334
>CREATE TABLE distributors (
2342
CLASS="PROGRAMLISTING"
2343
>CREATE TABLE distributors (
2344
did integer PRIMARY KEY,
2350
> Assign a literal constant default value for the column
2354
>, arrange for the default value of column
2358
> to be generated by selecting the next value
2359
of a sequence object, and make the default value of
2363
> be the time at which the row is
2367
CLASS="PROGRAMLISTING"
2368
>CREATE TABLE distributors (
2369
name varchar(40) DEFAULT 'Luso Films',
2370
did integer DEFAULT nextval('distributors_serial'),
2371
modtime timestamp DEFAULT current_timestamp
2379
> column constraints on the table
2383
>, one of which is explicitly
2387
CLASS="PROGRAMLISTING"
2388
>CREATE TABLE distributors (
2389
did integer CONSTRAINT no_null NOT NULL,
2390
name varchar(40) NOT NULL
2395
> Define a unique constraint for the <TT
2401
CLASS="PROGRAMLISTING"
2402
>CREATE TABLE distributors (
2404
name varchar(40) UNIQUE
2408
The same, specified as a table constraint:
2411
CLASS="PROGRAMLISTING"
2412
>CREATE TABLE distributors (
2420
> Create the same table, specifying 70% fill factor for both the table
2421
and its unique index:
2424
CLASS="PROGRAMLISTING"
2425
>CREATE TABLE distributors (
2428
UNIQUE(name) WITH (fillfactor=70)
2430
WITH (fillfactor=70);</PRE
2438
constraint that prevents any two circles from overlapping:
2441
CLASS="PROGRAMLISTING"
2442
>CREATE TABLE circles (
2444
EXCLUDE USING gist (c WITH &&)
2458
CLASS="PROGRAMLISTING"
2459
>CREATE TABLE cinemas (
2463
) TABLESPACE diskvol1;</PRE
2467
> Create a composite type and a typed table:
2469
CLASS="PROGRAMLISTING"
2470
>CREATE TYPE employee_type AS (name text, salary numeric);
2472
CREATE TABLE employees OF employee_type (
2474
salary WITH OPTIONS DEFAULT 1000
2482
NAME="SQL-CREATETABLE-COMPATIBILITY"
2490
> command conforms to the
2494
> standard, with exceptions listed below.
2502
>Temporary Tables</H3
2504
> Although the syntax of <TT
2506
>CREATE TEMPORARY TABLE</TT
2508
resembles that of the SQL standard, the effect is not the same. In the
2510
temporary tables are defined just once and automatically exist (starting
2511
with empty contents) in every session that needs them.
2516
requires each session to issue its own <TT
2520
> command for each temporary table to be used. This allows
2521
different sessions to use the same temporary table name for different
2522
purposes, whereas the standard's approach constrains all instances of a
2523
given temporary table name to have the same table structure.
2526
> The standard's definition of the behavior of temporary tables is
2527
widely ignored. <SPAN
2531
on this point is similar to that of several other SQL databases.
2534
> The standard's distinction between global and local temporary tables
2538
>, since that distinction
2539
depends on the concept of modules, which
2544
For compatibility's sake, <SPAN
2555
in a temporary table declaration, but they have no effect.
2561
> clause for temporary tables
2562
also resembles the SQL standard, but has some differences.
2566
> clause is omitted, SQL specifies that the
2567
default behavior is <TT
2569
>ON COMMIT DELETE ROWS</TT
2571
default behavior in <SPAN
2577
>ON COMMIT PRESERVE ROWS</TT
2582
> option does not exist in SQL.
2591
>Non-deferred Uniqueness Constraints</H3
2600
not deferrable, <SPAN
2604
uniqueness immediately whenever a row is inserted or modified.
2605
The SQL standard says that uniqueness should be enforced only at
2606
the end of the statement; this makes a difference when, for example,
2607
a single command updates multiple key values. To obtain
2608
standard-compliant behavior, declare the constraint as
2612
> but not deferred (i.e., <TT
2616
>). Be aware that this can be significantly slower than
2617
immediate uniqueness checking.
2626
>Column Check Constraints</H3
2628
> The SQL standard says that <TT
2631
> column constraints
2632
can only refer to the column they apply to; only <TT
2636
table constraints can refer to multiple columns.
2640
> does not enforce this
2641
restriction; it treats column and table check constraints alike.
2658
> constraint type is a
2686
non-constraint) is a <SPAN
2690
extension to the SQL standard that is included for compatibility with some
2691
other database systems (and for symmetry with the <TT
2695
> constraint). Since it is the default for any
2696
column, its presence is simply noise.
2707
> Multiple inheritance via the <TT
2714
> language extension.
2715
SQL:1999 and later define single inheritance using a
2716
different syntax and different semantics. SQL:1999-style
2717
inheritance is not yet supported by
2730
>Zero-column Tables</H3
2735
> allows a table of no columns
2736
to be created (for example, <TT
2738
>CREATE TABLE foo();</TT
2740
is an extension from the SQL standard, which does not allow zero-column
2741
tables. Zero-column tables are not in themselves very useful, but
2742
disallowing them creates odd special cases for <TT
2746
>, so it seems cleaner to ignore this spec restriction.
2767
extension; neither storage parameters nor OIDs are in the standard.
2781
> concept of tablespaces is not
2782
part of the standard. Hence, the clauses <TT
2788
>USING INDEX TABLESPACE</TT
2800
> Typed tables implement a subset of the SQL standard. According to
2801
the standard, a typed table has columns corresponding to the
2802
underlying composite type as well as one other column that is
2805
>"self-referencing column"</SPAN
2806
>. PostgreSQL does not
2807
support these self-referencing columns explicitly, but the same
2808
effect can be had using the OID feature.
2820
HREF="sql-altertable.html"
2823
HREF="sql-droptable.html"
2826
HREF="sql-createtablespace.html"
2827
>CREATE TABLESPACE</A
2829
HREF="sql-createtype.html"
2837
SUMMARY="Footer navigation table"
2848
HREF="sql-createserver.html"
2866
HREF="sql-createtableas.html"
2882
HREF="sql-commands.html"
2890
>CREATE TABLE AS</TD
b'\\ No newline at end of file'
added
removed
doc/src/sgml/html/sql-createtable.html
