Provided by:
manpages-zh_1.5.2-1_all 
NAME
CREATE TABLE -
SYNOPSIS
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ 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 | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
DESCRIPTION
CREATE TABLE
CREATE TABLE myschema.mytable ...
CREATE TABLE
1600
SQL
PARAMETERS
TEMPORARY TEMP
ON COMMIT
TEMPORARY TEMP GLOBAL LOCAL PostgreSQL Compatibility
[create_table(7)]
table_name
column_name
data_type
DEFAULT
DEFAULT
NULL
LIKE
INHERITS
INCLUDING DEFAULTS
INHERITS ( parent_table [, ... ] )
INHERITS
WITH OIDS
WITHOUT OIDS
OID OID OID WITHOUT OIDS WITH OIDS
WITHOUT OIDS OID OID 32 OID OID WITHOUT OIDS 4
CONSTRAINT constraint_name
NOT NULL
NULL
NULL
NULL
SQL
UNIQUE (column constraint)
UNIQUE ( column_name [, ... ] ) (table constraint)
UNIQUE
NULL
PRIMARY KEY (column constraint)
PRIMARY KEY ( column_name [, ... ] ) (table constraint)
NULL PRIMARY KEY UNIQUE NOT NULL
CHECK (expression)
CHECK
CHECK
REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column [, ... ] )
reftable refcolumn refcolumn reftable
MATCH FULL MATCH PARTIAL MATCH SIMPLE MATCH FULL NULL NULL
MATCH SIMPLE NULL NULLMATCH PARTIAL
ON DELETE ON UPDATE
NO ACTION
RESTRICT
NO ACTION
CASCADE
SET NULL
NULL
SET DEFAULT
REFERENCES REFERENCES NO ACTION CASCADE
DEFERRABLE
NOT DEFERRABLE
SET CONSTRAINTS [set_constraints(7)] NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED
INITIALLY IMMEDIATE INITIALLY DEFERRED SET CONSTRAINTS
[set_constraints(7)]
ON COMMIT
ON COMMIT
PRESERVE ROWS
DELETE ROWS
truncate(7)
DROP
NOTES
o
OID oid OID OID tableoid OID PostgreSQL OID tableoid
: WITHOUT OIDS OID
o PostgreSQL CREATE INDEX [create_index(7)]
o
EXAMPLES
films distributors
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
);
CREATE TABLE distributors (
did integer PRIMARY KEY DEFAULT nextval('serial'),
name varchar(40) NOT NULL CHECK (name <> '')
);
2
CREATE TABLE array (
vector int[][]
);
films
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)
);
CREATE TABLE distributors (
did integer CHECK (did > 100),
name varchar(40)
);
CREATE TABLE distributors (
did integer,
name varchar(40)
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);
films
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)
);
distributors
CREATE TABLE distributors (
did integer,
name varchar(40),
PRIMARY KEY(did)
);
CREATE TABLE distributors (
did integer PRIMARY KEY,
name varchar(40)
);
name did modtime
CREATE TABLE distributors (
name varchar(40) DEFAULT 'Luso Films',
did integer DEFAULT nextval('distributors_serial'),
modtime timestamp DEFAULT current_timestamp
);
distributors NOT NULL
CREATE TABLE distributors (
did integer CONSTRAINT no_null NOT NULL,
name varchar(40) NOT NULL
);
name
CREATE TABLE distributors (
did integer,
name varchar(40) UNIQUE
);
CREATE TABLE distributors (
did integer,
name varchar(40),
UNIQUE(name)
);
COMPATIBILITY
CREATE TABLE SQL92 SQL99
TEMPORARY TABLES
CREATE TEMPORARY TABLE SQL PostgreSQL CREATE TEMPORARY TABLE
PostgreSQL SQL
PostgreSQL PostgreSQL PostgreSQL GLOBAL LOCAL
ON COMMIT SQL ON COMMIT SQL ON COMMIT DELETE ROWS PostgreSQL ON
COMMIT PRESERVE ROWS SQL ON COMMIT DROP
COLUMN CHECK CONSTRAINTS
SQL CHECK CHECK PostgreSQL
NULL ``CONSTRAINT'' NULL
NULL "" PostgreSQL SQL NOT NULL
INHERITANCE
INHERITS PostgreSQL SQL99 SQL92 SQL99 PostgreSQL
OBJECT IDS ID
PostgreSQL OID
ZERO-COLUMN TABLES
PostgreSQL CREATE TABLE foo(); SQL ALTER TABLE DROP COLUMN
SEE ALSO
ALTER TABLE [alter_table(7)], DROP TABLE [drop_table(l)]
Postgresql <laser@pgsqldb.org>