2
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.90.4.1 2005-01-22 23:23:53 momjian Exp $
3
PostgreSQL documentation
6
<refentry id="SQL-CREATETABLE">
8
<refentrytitle id="sql-createtable-title">CREATE TABLE</refentrytitle>
9
<refmiscinfo>SQL - Language Statements</refmiscinfo>
13
<refname>CREATE TABLE</refname>
14
<refpurpose>define a new table</refpurpose>
17
<indexterm zone="sql-createtable">
18
<primary>CREATE TABLE</primary>
23
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable> (
24
{ <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ DEFAULT <replaceable>default_expr</> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
25
| <replaceable>table_constraint</replaceable>
26
| LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ]
28
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
29
[ WITH OIDS | WITHOUT OIDS ]
30
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
31
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
33
where <replaceable class="PARAMETER">column_constraint</replaceable> is:
35
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
38
UNIQUE [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
39
PRIMARY KEY [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
40
CHECK (<replaceable class="PARAMETER">expression</replaceable>) |
41
REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
42
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
43
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
45
and <replaceable class="PARAMETER">table_constraint</replaceable> is:
47
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
48
{ UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
49
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
50
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
51
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
52
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
53
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
58
<refsect1 id="SQL-CREATETABLE-description">
59
<title>Description</title>
62
<command>CREATE TABLE</command> will create a new, initially empty table
63
in the current database. The table will be owned by the user issuing the
68
If a schema name is given (for example, <literal>CREATE TABLE
69
myschema.mytable ...</>) then the table is created in the
70
specified schema. Otherwise it is created in the current schema.
71
Temporary tables exist in a special schema, so a schema name may not be
72
given when creating a temporary table.
73
The table name must be distinct from the name of any other table,
74
sequence, index, or view in the same schema.
78
<command>CREATE TABLE</command> also automatically creates a data
79
type that represents the composite type corresponding
80
to one row of the table. Therefore, tables cannot have the same
81
name as any existing data type in the same schema.
85
The optional constraint clauses specify constraints (tests) that
86
new or updated rows must satisfy for an insert or update operation
87
to succeed. A constraint is an SQL object that helps define the
88
set of valid values in the table in various ways.
92
There are two ways to define constraints: table constraints and
93
column constraints. A column constraint is defined as part of a
94
column definition. A table constraint definition is not tied to a
95
particular column, and it can encompass more than one column.
96
Every column constraint can also be written as a table constraint;
97
a column constraint is only a notational convenience for use when the
98
constraint only affects one column.
103
<title>Parameters</title>
108
<term><literal>TEMPORARY</> or <literal>TEMP</></term>
111
If specified, the table is created as a temporary table.
112
Temporary tables are automatically dropped at the end of a
113
session, or optionally at the end of the current transaction
114
(see <literal>ON COMMIT</literal> below). Existing permanent
115
tables with the same name are not visible to the current session
116
while the temporary table exists, unless they are referenced
117
with schema-qualified names. Any indexes created on a temporary
118
table are automatically temporary as well.
122
Optionally, <literal>GLOBAL</literal> or <literal>LOCAL</literal>
123
can be written before <literal>TEMPORARY</> or <literal>TEMP</>.
124
This makes no difference in <productname>PostgreSQL</>, but see
125
<xref linkend="sql-createtable-compatibility"
126
endterm="sql-createtable-compatibility-title">.
132
<term><replaceable class="PARAMETER">table_name</replaceable></term>
135
The name (optionally schema-qualified) of the table to be created.
141
<term><replaceable class="PARAMETER">column_name</replaceable></term>
144
The name of a column to be created in the new table.
150
<term><replaceable class="PARAMETER">data_type</replaceable></term>
153
The data type of the column. This may include array
154
specifiers. For more information on the data types supported by
155
<productname>PostgreSQL</productname>, refer to <xref
162
<term><literal>DEFAULT
163
<replaceable>default_expr</replaceable></literal></term>
166
The <literal>DEFAULT</> clause assigns a default data value for
167
the column whose column definition it appears within. The value
168
is any variable-free expression (subqueries and cross-references
169
to other columns in the current table are not allowed). The
170
data type of the default expression must match the data type of the
175
The default expression will be used in any insert operation that
176
does not specify a value for the column. If there is no default
177
for a column, then the default is null.
183
<term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term>
186
The optional <literal>INHERITS</> clause specifies a list of
187
tables from which the new table automatically inherits all
192
Use of <literal>INHERITS</> creates a persistent relationship
193
between the new child table and its parent table(s). Schema
194
modifications to the parent(s) normally propagate to children
195
as well, and by default the data of the child table is included in
196
scans of the parent(s).
200
If the same column name exists in more than one parent
201
table, an error is reported unless the data types of the columns
202
match in each of the parent tables. If there is no conflict,
203
then the duplicate columns are merged to form a single column in
204
the new table. If the column name list of the new table
205
contains a column name that is also inherited, the data type must
206
likewise match the inherited column(s), and the column
207
definitions are merged into one. However, inherited and new
208
column declarations of the same name need not specify identical
209
constraints: all constraints provided from any declaration are
210
merged together and all are applied to the new table. If the
211
new table explicitly specifies a default value for the column,
212
this default overrides any defaults from inherited declarations
213
of the column. Otherwise, any parents that specify default
214
values for the column must all specify the same default, or an
215
error will be reported.
219
<productname>PostgreSQL</> automatically allows the
220
created table to inherit
221
functions on tables above it in the inheritance hierarchy; that
222
is, if we create table <literal>foo</literal> inheriting from
223
<literal>bar</literal>, then functions that accept the tuple
224
type <literal>bar</literal> can also be applied to instances of
225
<literal>foo</literal>. (Currently, this works reliably for
226
functions on the first or only parent table, but not so well for
227
functions on additional parents.)
234
<term><literal>LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING } DEFAULTS ]</literal></term>
237
The <literal>LIKE</literal> clause specifies a table from which
238
the new table automatically copies all column names, their data types,
239
and their not-null constraints.
242
Unlike <literal>INHERITS</literal>, the new table and original table
243
are completely decoupled after creation is complete. Changes to the
244
original table will not be applied to the new table, and it is not
245
possible to include data of the new table in scans of the original
249
Default expressions for the copied column definitions will only be
250
copied if <literal>INCLUDING DEFAULTS</literal> is specified. The
251
default behavior is to exclude default expressions, resulting in
252
all columns of the new table having null defaults.
258
<term><literal>WITH OIDS</></term>
259
<term><literal>WITHOUT OIDS</></term>
262
This optional clause specifies whether rows of the new table
263
should have OIDs (object identifiers) assigned to them. If
264
neither <literal>WITH OIDS</literal> nor <literal>WITHOUT
265
OIDS</literal> is specified, the default value depends upon the
266
<xref linkend="guc-default-with-oids"> configuration parameter. (If
267
the new table inherits from any tables that have OIDs, then
268
<literal>WITH OIDS</> is forced even if the command says
269
<literal>WITHOUT OIDS</>.)
273
If <literal>WITHOUT OIDS</literal> is specified or implied, the new
274
table does not store OIDs and no OID will be assigned for a row inserted
275
into it. This is generally considered worthwhile, since it
276
will reduce OID consumption and thereby postpone the wraparound
277
of the 32-bit OID counter. Once the counter wraps around, OIDs
278
can no longer be assumed to be unique, which makes them
279
considerably less useful. In addition, excluding OIDs from a
280
table reduces the space required to store the table on disk by
281
4 bytes per row (on most machines), slightly improving performance.
285
To remove OIDs from a table after it has been created, use <xref
286
linkend="sql-altertable" endterm="sql-altertable-title">.
292
<term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
295
An optional name for a column or table constraint. If not specified,
296
the system generates a name.
302
<term><literal>NOT NULL</></term>
305
The column is not allowed to contain null values.
311
<term><literal>NULL</></term>
314
The column is allowed to contain null values. This is the default.
318
This clause is only provided for compatibility with
319
non-standard SQL databases. Its use is discouraged in new
326
<term><literal>UNIQUE</> (column constraint)</term>
327
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
331
The <literal>UNIQUE</literal> constraint specifies that a
332
group of one or more columns of a table may contain
333
only unique values. The behavior of the unique table constraint
334
is the same as that for column constraints, with the additional
335
capability to span multiple columns.
339
For the purpose of a unique constraint, null values are not
344
Each unique table constraint must name a set of columns that is
345
different from the set of columns named by any other unique or
346
primary key constraint defined for the table. (Otherwise it
347
would just be the same constraint listed twice.)
353
<term><literal>PRIMARY KEY</> (column constraint)</term>
354
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
357
The primary key constraint specifies that a column or columns of a table
358
may contain only unique (non-duplicate), nonnull values.
359
Technically, <literal>PRIMARY KEY</literal> is merely a
360
combination of <literal>UNIQUE</> and <literal>NOT NULL</>, but
361
identifying a set of columns as primary key also provides
362
metadata about the design of the schema, as a primary key
363
implies that other tables
364
may rely on this set of columns as a unique identifier for rows.
368
Only one primary key can be specified for a table, whether as a
369
column constraint or a table constraint.
373
The primary key constraint should name a set of columns that is
374
different from other sets of columns named by any unique
375
constraint defined for the same table.
381
<term><literal>CHECK (<replaceable class="PARAMETER">expression</replaceable>)</literal></term>
384
The <literal>CHECK</> clause specifies an expression producing a
385
Boolean result which new or updated rows must satisfy for an
386
insert or update operation to succeed. Expressions evaluating
387
to TRUE or UNKNOWN succeed. Should any row of an insert or
388
update operation produce a FALSE result an error exception is
389
raised and the insert or update does not alter the database. A
390
check constraint specified as a column constraint should
391
reference that column's value only, while an expression
392
appearing in a table constraint may reference multiple columns.
396
Currently, <literal>CHECK</literal> expressions cannot contain
397
subqueries nor refer to variables other than columns of the
405
<term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> (column constraint)</term>
407
<term><literal>FOREIGN KEY ( <replaceable class="parameter">column</replaceable> [, ... ] )
408
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
409
[ MATCH <replaceable class="parameter">matchtype</replaceable> ]
410
[ ON DELETE <replaceable class="parameter">action</replaceable> ]
411
[ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal>
412
(table constraint)</term>
416
These clauses specify a foreign key constraint, which requires
417
that a group of one or more columns of the new table must only
418
contain values that match values in the referenced
419
column(s) of some row of the referenced table. If <replaceable
420
class="parameter">refcolumn</replaceable> is omitted, the
421
primary key of the <replaceable
422
class="parameter">reftable</replaceable> is used. The
423
referenced columns must be the columns of a unique or primary
424
key constraint in the referenced table.
428
A value inserted into the referencing column(s) is matched against the
429
values of the referenced table and referenced columns using the
430
given match type. There are three match types: <literal>MATCH
431
FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
432
SIMPLE</literal>, which is also the default. <literal>MATCH
433
FULL</> will not allow one column of a multicolumn foreign key
434
to be null unless all foreign key columns are null.
435
<literal>MATCH SIMPLE</literal> allows some foreign key columns
436
to be null while other parts of the foreign key are not
437
null. <literal>MATCH PARTIAL</> is not yet implemented.
441
In addition, when the data in the referenced columns is changed,
442
certain actions are performed on the data in this table's
443
columns. The <literal>ON DELETE</literal> clause specifies the
444
action to perform when a referenced row in the referenced table is
445
being deleted. Likewise, the <literal>ON UPDATE</literal>
446
clause specifies the action to perform when a referenced column
447
in the referenced table is being updated to a new value. If the
448
row is updated, but the referenced column is not actually
449
changed, no action is done. Referential actions other than the
450
<literal>NO ACTION</literal> check cannot be deferred, even if
451
the constraint is declared deferrable. There are the following possible
452
actions for each clause:
456
<term><literal>NO ACTION</literal></term>
459
Produce an error indicating that the deletion or update
460
would create a foreign key constraint violation.
461
If the constraint is deferred, this
462
error will be produced at constraint check time if there still
463
exist any referencing rows. This is the default action.
469
<term><literal>RESTRICT</literal></term>
472
Produce an error indicating that the deletion or update
473
would create a foreign key constraint violation.
474
This is the same as <literal>NO ACTION</literal> except that
475
the check is not deferrable.
481
<term><literal>CASCADE</literal></term>
484
Delete any rows referencing the deleted row, or update the
485
value of the referencing column to the new value of the
486
referenced column, respectively.
492
<term><literal>SET NULL</literal></term>
495
Set the referencing column(s) to null.
501
<term><literal>SET DEFAULT</literal></term>
504
Set the referencing column(s) to their default values.
512
If the referenced column(s) are changed frequently, it may be wise to
513
add an index to the foreign key column so that referential actions
514
associated with the foreign key column can be performed more
521
<term><literal>DEFERRABLE</literal></term>
522
<term><literal>NOT DEFERRABLE</literal></term>
525
This controls whether the constraint can be deferred. A
526
constraint that is not deferrable will be checked immediately
527
after every command. Checking of constraints that are
528
deferrable may be postponed until the end of the transaction
529
(using the <xref linkend="sql-set-constraints" endterm="sql-set-constraints-title"> command).
530
<literal>NOT DEFERRABLE</literal> is the default. Only foreign
531
key constraints currently accept this clause. All other
532
constraint types are not deferrable.
538
<term><literal>INITIALLY IMMEDIATE</literal></term>
539
<term><literal>INITIALLY DEFERRED</literal></term>
542
If a constraint is deferrable, this clause specifies the default
543
time to check the constraint. If the constraint is
544
<literal>INITIALLY IMMEDIATE</literal>, it is checked after each
545
statement. This is the default. If the constraint is
546
<literal>INITIALLY DEFERRED</literal>, it is checked only at the
547
end of the transaction. The constraint check time can be
548
altered with the <xref linkend="sql-set-constraints" endterm="sql-set-constraints-title"> command.
554
<term><literal>ON COMMIT</literal></term>
557
The behavior of temporary tables at the end of a transaction
558
block can be controlled using <literal>ON COMMIT</literal>.
559
The three options are:
563
<term><literal>PRESERVE ROWS</literal></term>
566
No special action is taken at the ends of transactions.
567
This is the default behavior.
573
<term><literal>DELETE ROWS</literal></term>
576
All rows in the temporary table will be deleted at the
577
end of each transaction block. Essentially, an automatic
578
<xref linkend="sql-truncate"> is done at each commit.
584
<term><literal>DROP</literal></term>
587
The temporary table will be dropped at the end of the current
598
<term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
601
The <replaceable class="PARAMETER">tablespace</replaceable> is the name
602
of the tablespace in which the new table is to be created.
604
<xref linkend="guc-default-tablespace"> is used, or the database's
605
default tablespace if <varname>default_tablespace</> is an empty
612
<term><literal>USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
615
This clause allows selection of the tablespace in which the index
616
associated with a <literal>UNIQUE</literal> or <literal>PRIMARY
617
KEY</literal> constraint will be created.
619
<xref linkend="guc-default-tablespace"> is used, or the database's
620
default tablespace if <varname>default_tablespace</> is an empty
629
<refsect1 id="SQL-CREATETABLE-notes">
633
Using OIDs in new applications is not recommended: where
634
possible, using a <literal>SERIAL</literal> or other sequence
635
generator as the table's primary key is preferred. However, if
636
your application does make use of OIDs to identify specific
637
rows of a table, it is recommended to create a unique constraint
638
on the <structfield>oid</> column of that table, to ensure that
639
OIDs in the table will indeed uniquely identify rows even after
640
counter wraparound. Avoid assuming that OIDs are unique across
641
tables; if you need a database-wide unique identifier, use the
642
combination of <structfield>tableoid</> and row OID for the
648
The use of <literal>WITHOUT OIDS</literal> is not recommended
649
for tables with no primary key, since without either an OID or a
650
unique data key, it is difficult to identify specific rows.
655
<productname>PostgreSQL</productname> automatically creates an
656
index for each unique constraint and primary key constraint to
657
enforce uniqueness. Thus, it is not necessary to create an
658
index explicitly for primary key columns. (See <xref
659
linkend="sql-createindex" endterm="sql-createindex-title"> for more information.)
663
Unique constraints and primary keys are not inherited in the
664
current implementation. This makes the combination of
665
inheritance and unique constraints rather dysfunctional.
669
A table cannot have more than 1600 columns. (In practice, the
670
effective limit is lower because of tuple-length constraints.)
676
<refsect1 id="SQL-CREATETABLE-examples">
677
<title>Examples</title>
680
Create table <structname>films</> and table
681
<structname>distributors</>:
685
code char(5) CONSTRAINT firstkey PRIMARY KEY,
686
title varchar(40) NOT NULL,
687
did integer NOT NULL,
690
len interval hour to minute
695
CREATE TABLE distributors (
696
did integer PRIMARY KEY DEFAULT nextval('serial'),
697
name varchar(40) NOT NULL CHECK (name <> '')
703
Create a table with a 2-dimensional array:
706
CREATE TABLE array_int (
713
Define a unique table constraint for the table
714
<literal>films</literal>. Unique table constraints can be defined
715
on one or more columns of the table.
724
len interval hour to minute,
725
CONSTRAINT production UNIQUE(date_prod)
731
Define a check column constraint:
734
CREATE TABLE distributors (
735
did integer CHECK (did > 100),
742
Define a check table constraint:
745
CREATE TABLE distributors (
748
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
754
Define a primary key table constraint for the table
755
<structname>films</>. Primary key table constraints can be defined
756
on one or more columns of the table.
765
len interval hour to minute,
766
CONSTRAINT code_title PRIMARY KEY(code,title)
772
Define a primary key constraint for table
773
<structname>distributors</>. The following two examples are
774
equivalent, the first using the table constraint syntax, the second
775
the column constraint syntax.
778
CREATE TABLE distributors (
786
CREATE TABLE distributors (
787
did integer PRIMARY KEY,
794
This assigns a literal constant default value for the column
795
<literal>name</literal>, arranges for the default value of column
796
<literal>did</literal> to be generated by selecting the next value
797
of a sequence object, and makes the default value of
798
<literal>modtime</literal> be the time at which the row is
802
CREATE TABLE distributors (
803
name varchar(40) DEFAULT 'Luso Films',
804
did integer DEFAULT nextval('distributors_serial'),
805
modtime timestamp DEFAULT current_timestamp
811
Define two <literal>NOT NULL</> column constraints on the table
812
<classname>distributors</classname>, one of which is explicitly
816
CREATE TABLE distributors (
817
did integer CONSTRAINT no_null NOT NULL,
818
name varchar(40) NOT NULL
824
Define a unique constraint for the <literal>name</literal> column:
827
CREATE TABLE distributors (
829
name varchar(40) UNIQUE
833
The above is equivalent to the following specified as a table constraint:
836
CREATE TABLE distributors (
845
Create table <structname>cinemas</> in tablespace <structname>diskvol1</>:
848
CREATE TABLE cinemas (
852
) TABLESPACE diskvol1;
858
<refsect1 id="SQL-CREATETABLE-compatibility">
859
<title id="SQL-CREATETABLE-compatibility-title">Compatibility</title>
862
The <command>CREATE TABLE</command> command conforms to SQL-92 and
863
to a subset of SQL:1999, with exceptions listed below.
867
<title>Temporary Tables</title>
870
Although the syntax of <literal>CREATE TEMPORARY TABLE</literal>
871
resembles that of the SQL standard, the effect is not the same. In the
873
temporary tables are defined just once and automatically exist (starting
874
with empty contents) in every session that needs them.
875
<productname>PostgreSQL</productname> instead
876
requires each session to issue its own <literal>CREATE TEMPORARY
877
TABLE</literal> command for each temporary table to be used. This allows
878
different sessions to use the same temporary table name for different
879
purposes, whereas the standard's approach constrains all instances of a
880
given temporary table name to have the same table structure.
884
The standard's definition of the behavior of temporary tables is
885
widely ignored. <productname>PostgreSQL</productname>'s behavior
886
on this point is similar to that of several other SQL databases.
890
The standard's distinction between global and local temporary tables
891
is not in <productname>PostgreSQL</productname>, since that distinction
892
depends on the concept of modules, which
893
<productname>PostgreSQL</productname> does not have.
894
For compatibility's sake, <productname>PostgreSQL</productname> will
895
accept the <literal>GLOBAL</literal> and <literal>LOCAL</literal> keywords
896
in a temporary table declaration, but they have no effect.
900
The <literal>ON COMMIT</literal> clause for temporary tables
901
also resembles the SQL standard, but has some differences.
902
If the <literal>ON COMMIT</> clause is omitted, SQL specifies that the
903
default behavior is <literal>ON COMMIT DELETE ROWS</>. However, the
904
default behavior in <productname>PostgreSQL</productname> is
905
<literal>ON COMMIT PRESERVE ROWS</literal>. The <literal>ON COMMIT
906
DROP</literal> option does not exist in SQL.
911
<title>Column Check Constraints</title>
914
The SQL standard says that <literal>CHECK</> column constraints
915
may only refer to the column they apply to; only <literal>CHECK</>
916
table constraints may refer to multiple columns.
917
<productname>PostgreSQL</productname> does not enforce this
918
restriction; it treats column and table check constraints alike.
923
<title><literal>NULL</literal> <quote>Constraint</quote></title>
926
The <literal>NULL</> <quote>constraint</quote> (actually a
927
non-constraint) is a <productname>PostgreSQL</productname>
928
extension to the SQL standard that is included for compatibility with some
929
other database systems (and for symmetry with the <literal>NOT
930
NULL</literal> constraint). Since it is the default for any
931
column, its presence is simply noise.
936
<title>Inheritance</title>
939
Multiple inheritance via the <literal>INHERITS</literal> clause is
940
a <productname>PostgreSQL</productname> language extension.
941
SQL:1999 (but not SQL-92) defines single inheritance using a
942
different syntax and different semantics. SQL:1999-style
943
inheritance is not yet supported by
944
<productname>PostgreSQL</productname>.
949
<title>Object IDs</title>
952
The <productname>PostgreSQL</productname> concept of OIDs is not
958
<title>Zero-column tables</title>
961
<productname>PostgreSQL</productname> allows a table of no columns
962
to be created (for example, <literal>CREATE TABLE foo();</>). This
963
is an extension from the SQL standard, which does not allow zero-column
964
tables. Zero-column tables are not in themselves very useful, but
965
disallowing them creates odd special cases for <command>ALTER TABLE
966
DROP COLUMN</>, so it seems cleaner to ignore this spec restriction.
971
<title>Tablespaces</title>
974
The <productname>PostgreSQL</productname> concept of tablespaces is not
975
part of the standard. Hence, the clauses <literal>TABLESPACE</literal>
976
and <literal>USING INDEX TABLESPACE</literal> are extensions.
983
<title>See Also</title>
985
<simplelist type="inline">
986
<member><xref linkend="sql-altertable" endterm="sql-altertable-title"></member>
987
<member><xref linkend="sql-droptable" endterm="sql-droptable-title"></member>
988
<member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
993
<!-- Keep this comment at the end of the file
998
sgml-minimize-attributes:nil
999
sgml-always-quote-attributes:t
1002
sgml-parent-document:nil
1003
sgml-default-dtd-file:"../reference.ced"
1004
sgml-exposed-tags:nil
1005
sgml-local-catalogs:"/usr/lib/sgml/catalog"
1006
sgml-local-ecat-files:nil
added
removed
doc/src/sgml/ref/create_table.sgml
