CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
NAME
CREATE TABLE - define a new table
SYNOPSIS
CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
| table_constraint } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
DESCRIPTION
CREATE TABLE will create a new, initially empty table in the
current database. The table will be owned by the user
issuing the command.
If a schema name is given (for example, CREATE TABLE
myschema.mytable ...) then the table is created in the
specified schema. Otherwise it is created in the current
schema (the one at the front of the search path; see
CURRENT_SCHEMA()). TEMP tables exist in a special schema,
so a schema name may not be given when creating a TEMP
table. The table name must be distinct from the name of any
other table, sequence, index, or view in the same schema.
CREATE TABLE also automatically creates a data type that
represents the tuple type (structure type) corresponding to
one row of the table. Therefore, tables cannot have the same
name as any existing data type in the same schema.
A table cannot have more than 1600 columns. (In practice,
the effective limit is lower because of tuple-length
Page 1 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
constraints).
The optional constraint clauses specify constraints (or
tests) that new or updated rows must satisfy for an insert
or update operation to succeed. A constraint is a named
rule: an SQL object which helps define valid sets of values
by putting limits on the results of insert, update, or
delete operations performed on a table.
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 if the constraint only affects one
column.
PARAMETERS
[LOCAL] TEMPORARY or [LOCAL] TEMP
If specified, the table is created as a temporary
table. Temporary tables are automatically dropped at
the end of a session. 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.
The LOCAL word is optional. But see under Compatibility
[create_table(l)].
table_name
The name (optionally schema-qualified) of the table to
be created.
column_name
The name of a column to be created in the new table.
data_type
The data type of the column. This may include array
specifiers. Refer to the User's Guide for further
information about data types and arrays.
DEFAULT
The DEFAULT clause assigns a default data value for the
column whose column definition it appears within. The
value is any variable-free expression (subselects 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.
Page 2 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
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.
INHERITS ( parent_table [, ... ] )
The optional INHERITS clause specifies a list of tables
from which the new table automatically inherits all
columns. 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 that is also inherited, the data type
must likewise match the inherited column(s), and the
column definitions are merged into one. However,
inherited and new column declarations of the same name
need not specify identical constraints: all constraints
provided from any declaration are merged together and
all are applied to the new table. 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.
WITH OIDS or WITHOUT OIDS
This optional clause specifies whether rows of the new
table should have OIDs (object identifiers) assigned to
them. The default is to have OIDs. (If the new table
inherits from any tables that have OIDs, then WITH OIDS
is forced even if the command says WITHOUT OIDS.)
Specifying WITHOUT OIDS allows the user to suppress
generation of OIDs for rows of a table. This may be
worthwhile for large tables, since it will reduce OID
consumption and thereby postpone wraparound of the 32-
bit OID counter. Once the counter wraps around,
uniqueness of OIDs can no longer be assumed, which
considerably reduces their usefulness.
CONSTRAINT constraint_name
An optional name for a column or table constraint. If
not specified, the system generates a name.
NOT NULL
The column is not allowed to contain NULL values.
NULL The column is allowed to contain NULL values. This is
the default.
Page 3 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
This clause is only available for compatibility with
non-standard SQL databases. Its use is discouraged in
new applications.
UNIQUE (column constraint)
UNIQUE ( column_name [, ... ] ) (table constraint)
The UNIQUE constraint specifies a rule that a group of
one or more distinct columns of a table may 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.
For the purpose of a unique constraint, NULL values are
not considered equal.
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.)
PRIMARY KEY (column constraint)
PRIMARY KEY ( column_name [, ... ] ) (table constraint)
The primary key constraint specifies that a column or
columns of a table may contain only unique (non-
duplicate), non-NULL values. Technically, PRIMARY KEY
is merely a combination of UNIQUE and NOT NULL, but
identifying a set of columns as primary key also
provides meta-data about the design of the schema, as a
primary key implies that other tables may rely on this
set of columns as a unique identifier for rows.
Only one primary key can be specified for a table,
whether as a column constraint or a table constraint.
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.
CHECK (expression)
CHECK clauses specify integrity constraints or tests
which new or updated rows must satisfy for an insert or
update operation to succeed. Each constraint must be an
expression producing a Boolean result. A condition
appearing within a column definition should reference
that column's value only, while a condition appearing
as a table constraint may reference multiple columns.
Currently, CHECK expressions cannot contain subselects
Page 4 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
nor refer to variables other than columns of the
current row.
DELETE action ] [ ON UPDATE action ] (column constraint)
REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON
FOREIGN KEY ( column [, ... ] )
The REFERENCES column constraint specifies that a group
of one or more columns of the new table must only
contain values which match against values in the
referenced column(s) refcolumn of the referenced table
reftable. If refcolumn is omitted, the primary key of
the reftable is used. The referenced columns must be
the columns of a unique or primary key constraint in
the referenced table.
A value added to these columns is matched against the
values of the referenced table and referenced columns
using the given match type. There are three match
types: MATCH FULL, MATCH PARTIAL, and a default match
type if none is specified. MATCH FULL will not allow
one column of a multicolumn foreign key to be NULL
unless all foreign key columns are NULL. The default
match type allows some foreign key columns to be NULL
while other parts of the foreign key are not NULL.
MATCH PARTIAL is not yet implemented.
In addition, when the data in the referenced columns is
changed, certain actions are performed on the data in
this table's columns. The ON DELETE clause specifies
the action to do when a referenced row in the
referenced table is being deleted. Likewise, the ON
UPDATE 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. There are the following possible actions for each
clause:
NO ACTION
Produce an error indicating that the deletion or
update would create a foreign key constraint
violation. This is the default action.
RESTRICT
Same as NO ACTION.
CASCADE
Delete any rows referencing the deleted row, or
update the value of the referencing column to the
new value of the referenced column, respectively.
Page 5 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
SET NULL
Set the referencing column values to NULL.
SET DEFAULT
Set the referencing column values to their default
value.
If primary key column is updated frequently, it may be wise
to add an index to the REFERENCES column so that NO ACTION
and CASCADE actions associated with the REFERENCES column
can be more efficiently performed.
DEFERRABLE or NOT DEFERRABLE
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 may be postponed until
the end of the transaction (using the SET CONSTRAINTS
[set_constraints(l)] command). NOT DEFERRABLE is the
default. Only foreign key constraints currently accept
this clause. All other constraint types are not
deferrable.
INITIALLY IMMEDIATE or INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies
the default time to check the constraint. If the
constraint is INITIALLY IMMEDIATE, it is checked after
each statement. This is the default. If the constraint
is INITIALLY DEFERRED, it is checked only at the end of
the transaction. The constraint check time can be
altered with the SET CONSTRAINTS [set_constraints(l)]
command.
DIAGNOSTICS
CREATE TABLE
Message returned if table is successfully created.
ERROR
Message returned if table creation failed. This is usually
accompanied by some descriptive text, such as: ERROR:
Relation 'table' already exists, which occurs at run time if
the table specified already exists in the database.
NOTES
o Whenever an application makes use of OIDs to identify
specific rows of a table, it is recommended to create a
unique constraint on the oid 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
Page 6 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
database-wide unique identifier, use the combination of
tableoid and row OID for the purpose. (It is likely that
future PostgreSQL releases will use a separate OID counter
for each table, so that it will be necessary, not
optional, to include tableoid to have a unique identifier
database-wide.)
Tip: The use of WITHOUT OIDS 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.
o PostgreSQL automatically creates an index for each unique
constraint and primary key constraint to enforce the
uniqueness. Thus, it is not necessary to create an
explicit index for primary key columns. (See CREATE INDEX
[create_index(l)] for more information.)
o The SQL92 standard says that CHECK column constraints may
only refer to the column they apply to; only CHECK table
constraints may refer to multiple columns. PostgreSQL does
not enforce this restriction; it treats column and table
check constraints alike.
o Unique constraints and primary keys are not inherited in
the current implementation. This makes the combination of
inheritance and unique constraints rather dysfunctional.
EXAMPLES
Create table films and table distributors:
CREATE TABLE films (
code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
title CHARACTER VARYING(40) NOT NULL,
did DECIMAL(3) NOT NULL,
date_prod DATE,
kind CHAR(10),
len INTERVAL HOUR TO MINUTE
);
CREATE TABLE distributors (
did DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'),
name VARCHAR(40) NOT NULL CHECK (name <> '')
);
Create a table with a 2-dimensional array:
CREATE TABLE array (
vector INT[][]
Page 7 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
);
Define a unique table constraint for the table films. Unique
table constraints can be defined on one or more columns of
the table:
CREATE TABLE films (
code CHAR(5),
title VARCHAR(40),
did DECIMAL(3),
date_prod DATE,
kind VARCHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT production UNIQUE(date_prod)
);
Define a check column constraint:
CREATE TABLE distributors (
did DECIMAL(3) CHECK (did > 100),
name VARCHAR(40)
);
Define a check table constraint:
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40)
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);
Define a primary key table constraint for the table films.
Primary key table constraints can be defined on one or more
columns of the table.
CREATE TABLE films (
code CHAR(5),
title VARCHAR(40),
did DECIMAL(3),
date_prod DATE,
kind VARCHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
Define a primary key constraint for table distributors. The
following two examples are equivalent, the first using the
Page 8 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
table constraint syntax, the second the column constraint
notation.
CREATE TABLE distributors (
did DECIMAL(3),
name CHAR VARYING(40),
PRIMARY KEY(did)
);
CREATE TABLE distributors (
did DECIMAL(3) PRIMARY KEY,
name VARCHAR(40)
);
This assigns a literal constant default value for the column
name, and arranges for the default value of column did to be
generated by selecting the next value of a sequence object.
The default value of modtime will be the time at which the
row is inserted.
CREATE TABLE distributors (
name VARCHAR(40) DEFAULT 'luso films',
did INTEGER DEFAULT NEXTVAL('distributors_serial'),
modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Define two NOT NULL column constraints on the table
distributors, one of which is explicitly given a name:
CREATE TABLE distributors (
did DECIMAL(3) CONSTRAINT no_null NOT NULL,
name VARCHAR(40) NOT NULL
);
Define a unique constraint for the name column:
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40) UNIQUE
);
The above is equivalent to the following specified as a
table constraint:
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40),
UNIQUE(name)
Page 9 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
);
COMPATIBILITY
The CREATE TABLE conforms to SQL92 Intermediate and to a
subset of SQL99, with exceptions listed below and in the
descriptions above.
TEMPORARY TABLES
In addition to the local temporary table, SQL92 also defines
a CREATE GLOBAL TEMPORARY TABLE statement. Global temporary
tables are also visible to other sessions.
For temporary tables, there is an optional ON COMMIT clause:
CREATE { GLOBAL | LOCAL } TEMPORARY TABLE table ( ... ) [ ON COMMIT { DELETE | PRESERVE } ROWS ]
The ON COMMIT clause specifies whether or not the temporary
table should be emptied of rows whenever COMMIT is executed.
If the ON COMMIT clause is omitted, SQL92 specifies that the
default is ON COMMIT DELETE ROWS. However, the behavior of
PostgreSQL is always like ON COMMIT PRESERVE ROWS.
NULL ``CONSTRAINT''
The NULL ``constraint'' (actually a non-constraint) is a
PostgreSQL extension to SQL92 that is included for
compatibility with some other RDBMS (and for symmetry with
the NOT NULL constraint). Since it is the default for any
column, its presence is simply noise.
ASSERTIONS
An assertion is a special type of integrity constraint and
shares the same namespace as other constraints. However, an
assertion is not necessarily dependent on one particular
table as constraints are, so SQL92 provides the CREATE
ASSERTION statement as an alternate method for defining a
constraint:
CREATE ASSERTION name CHECK ( condition )
PostgreSQL does not implement assertions at present.
INHERITANCE
Multiple inheritance via the INHERITS clause is a PostgreSQL
language extension. SQL99 (but not SQL92) defines single
inheritance using a different syntax and different
semantics. SQL99-style inheritance is not yet supported by
PostgreSQL.
OBJECT IDS
The PostgreSQL concept of OIDs is not standard.
Page 10 (printed 3/24/03)
CREATE TABLE(lSQL - Language Statements (2002-11-2CREATE TABLE(l)
SEE ALSO
ALTER TABLE [alter_table(l)], DROP TABLE [drop_table(l)]
Page 11 (printed 3/24/03)
