create_table.7

来自「PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开」· 7 代码 · 共 719 行 · 第 1/2 页

.TP
\fBWITH OIDS\fR
.TP
\fBWITHOUT OIDS\fR
These are obsolescent syntaxes equivalent to WITH (OIDS)
and WITH (OIDS=FALSE), respectively. If you wish to give
both an OIDS setting and storage parameters, you must use
the WITH ( ... ) syntax; see above.
.TP
\fBON COMMIT\fR
The behavior of temporary tables at the end of a transaction
block can be controlled using ON COMMIT.
The three options are:
.RS
.TP
\fBPRESERVE ROWS\fR
No special action is taken at the ends of transactions.
This is the default behavior.
.TP
\fBDELETE ROWS\fR
All rows in the temporary table will be deleted at the end
of each transaction block. Essentially, an automatic TRUNCATE [\fBtruncate\fR(7)] is done
at each commit.
.TP
\fBDROP\fR
The temporary table will be dropped at the end of the current
transaction block.
.RE
.PP
.TP
\fBTABLESPACE \fItablespace\fB\fR
The \fItablespace\fR is the name
of the tablespace in which the new table is to be created.
If not specified,
default_tablespace is used, or the database's
default tablespace if default_tablespace is an empty
string.
.TP
\fBUSING INDEX TABLESPACE \fItablespace\fB\fR
This clause allows selection of the tablespace in which the index
associated with a UNIQUE or PRIMARY
KEY constraint will be created.
If not specified,
default_tablespace is used, or the database's
default tablespace if default_tablespace is an empty
string.
.SS "STORAGE PARAMETERS"
.PP
The WITH clause can specify \fIstorage parameters\fR
for tables, and for indexes associated with a UNIQUE or
PRIMARY KEY constraint. Storage parameters for
indexes are documented in CREATE INDEX [\fBcreate_index\fR(7)]. The only storage parameter currently
available for tables is:
.TP
\fBFILLFACTOR\fR
The fillfactor for a table is a percentage between 10 and 100.
100 (complete packing) is the default. When a smaller fillfactor
is specified, \fBINSERT\fR 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 \fBUPDATE\fR
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.
.SH "NOTES"
.PP
Using OIDs in new applications is not recommended: where
possible, using a SERIAL or other sequence
generator as the table's 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 \fBoid\fR 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 \fBtableoid\fR and row OID for the
purpose.
.sp
.RS
.B "Tip:"
The use of OIDS=FALSE 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.
.RE
.sp
.PP
PostgreSQL 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 CREATE INDEX [\fBcreate_index\fR(7)] for more information.)
.PP
Unique constraints and primary keys are not inherited in the
current implementation. This makes the combination of
inheritance and unique constraints rather dysfunctional.
.PP
A table cannot have more than 1600 columns. (In practice, the
effective limit is usually lower because of tuple-length constraints.)
.SH "EXAMPLES"
.PP
Create table \fBfilms\fR and table
\fBdistributors\fR:
.sp
.nf
CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);
.sp
.fi
.sp
.nf
CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);
.sp
.fi
.PP
Create a table with a 2-dimensional array:
.sp
.nf
CREATE TABLE array_int (
    vector  int[][]
);
.sp
.fi
.PP
Define a unique table constraint for the table
films. Unique table constraints can be defined
on one or more columns of the table.
.sp
.nf
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);
.sp
.fi
.PP
Define a check column constraint:
.sp
.nf
CREATE TABLE distributors (
    did     integer CHECK (did > 100),
    name    varchar(40)
);
.sp
.fi
.PP
Define a check table constraint:
.sp
.nf
CREATE TABLE distributors (
    did     integer,
    name    varchar(40)
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);
.sp
.fi
.PP
Define a primary key table constraint for the table
\fBfilms\fR:
.sp
.nf
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);
.sp
.fi
.PP
Define a primary key constraint for table
\fBdistributors\fR. The following two examples are
equivalent, the first using the table constraint syntax, the second
the column constraint syntax:
.sp
.nf
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);
.sp
.fi
.sp
.nf
CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);
.sp
.fi
.PP
Assign a literal constant default value for the column
name, arrange for the default value of column
did to be generated by selecting the next value
of a sequence object, and make the default value of
modtime be the time at which the row is
inserted:
.sp
.nf
CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);
.sp
.fi
.PP
Define two NOT NULL column constraints on the table
\fBdistributors\fR, one of which is explicitly
given a name:
.sp
.nf
CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);
.sp
.fi
.PP
Define a unique constraint for the name column:
.sp
.nf
CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);
.sp
.fi
The same, specified as a table constraint:
.sp
.nf
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);
.sp
.fi
.PP
Create the same table, specifying 70% fill factor for both the table
and its unique index:
.sp
.nf
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);
.sp
.fi
.PP
Create table \fBcinemas\fR in tablespace \fBdiskvol1\fR:
.sp
.nf
CREATE TABLE cinemas (
        id serial,
        name text,
        location text
) TABLESPACE diskvol1;
.sp
.fi
.SH "COMPATIBILITY"
.PP
The \fBCREATE TABLE\fR command conforms to the
SQL standard, with exceptions listed below.
.SS "TEMPORARY TABLES"
.PP
Although the syntax of CREATE TEMPORARY TABLE
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.
PostgreSQL instead
requires each session to issue its own CREATE TEMPORARY
TABLE 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's approach constrains all instances of a
given temporary table name to have the same table structure.
.PP
The standard's definition of the behavior of temporary tables is
widely ignored. PostgreSQL's behavior
on this point is similar to that of several other SQL databases.
.PP
The standard's distinction between global and local temporary tables
is not in PostgreSQL, since that distinction
depends on the concept of modules, which
PostgreSQL does not have.
For compatibility's sake, PostgreSQL will
accept the GLOBAL and LOCAL keywords
in a temporary table declaration, but they have no effect.
.PP
The ON COMMIT clause for temporary tables
also resembles the SQL standard, but has some differences.
If the ON COMMIT clause is omitted, SQL specifies that the
default behavior is ON COMMIT DELETE ROWS. However, the
default behavior in PostgreSQL is
ON COMMIT PRESERVE ROWS. The ON COMMIT
DROP option does not exist in SQL.
.SS "COLUMN CHECK CONSTRAINTS"
.PP
The SQL 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.
.SS "NULL ``CONSTRAINT''"
.PP
The NULL ``constraint'' (actually a
non-constraint) is a PostgreSQL
extension to the SQL standard that is included for compatibility with some
other database systems (and for symmetry with the NOT
NULL constraint). Since it is the default for any
column, its presence is simply noise.
.SS "INHERITANCE"
.PP
Multiple inheritance via the INHERITS clause is
a PostgreSQL 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
PostgreSQL.
.SS "ZERO-COLUMN TABLES"
.PP
PostgreSQL allows a table of no columns
to be created (for example, 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 \fBALTER TABLE
DROP COLUMN\fR, so it seems cleaner to ignore this spec restriction.
.SS "WITH CLAUSE"
.PP
The WITH clause is a PostgreSQL
extension; neither storage parameters nor OIDs are in the standard.
.SS "TABLESPACES"
.PP
The PostgreSQL concept of tablespaces is not
part of the standard. Hence, the clauses TABLESPACE
and USING INDEX TABLESPACE are extensions.
.SH "SEE ALSO"
ALTER TABLE [\fBalter_table\fR(7)], DROP TABLE [\fBdrop_table\fR(l)], CREATE TABLESPACE [\fBcreate_tablespace\fR(l)]