PostgreSQL CREATE TABLE(EN)

长平狐 发布于 2012/09/06 15:10
阅读 212
收藏 0

Name

CREATE TABLE -- define a new table

Synopsis

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ like_option ... ] } [ , ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH ( storage_parameter [= value ] [ , ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name OF type_name [ ( { column_name WITH OPTIONS [ column_constraint [ ... ] ] | table_constraint } [ , ... ] ) ] [ WITH ( storage_parameter [= value ] [ , ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] where column_constraint is : [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( exssion ) | DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | 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 ] { CHECK ( exssion ) | UNIQUE ( column_name [ , ... ] ) index_parameters | PRIMARY KEY ( column_name [ , ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [ , ... ] ) index_parameters [ WHERE ( dicate ) ] | 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 ] and like_option is : { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are: [ WITH ( storage_parameter [= value ] [ , ... ] ) ] [ USING INDEX TABLESPACE tablespace ] exclude_element in an EXCLUDE constraint is : { column | ( exssion ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Examples

Create table films and table 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 <> '' ));

Create a table with a 2-dimensional array:

CREATE TABLE array_int ( vector int [][] );

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 integer , 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 integer CHECK (did > 100 ), name varchar( 40 ));

Define a check table constraint:

CREATE TABLE distributors ( did integer , name varchar( 40 ) CONSTRAINT con1 CHECK (did > 100 AND name <> '' ));

Define a primary key table constraint for the table 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));

Define a primary key constraint for table distributors. The following two examples are equivalent, the first using the table constraint syntax, the second the column constraint syntax:

CREATE TABLE distributors ( did integer , name varchar( 40 ), PRIMARY KEY (did)); CREATE TABLE distributors ( did integer PRIMARY KEY , name varchar( 40 ));

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:

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 integer CONSTRAINT no_null NOT NULL , name varchar( 40) NOT NULL );

Define a unique constraint for the name column:

CREATE TABLE distributors ( did integer , name varchar( 40) UNIQUE );

The same, specified as a table constraint:

CREATE TABLE distributors ( did integer , name varchar( 40 ), UNIQUE (name));

Create the same table, specifying 70% fill factor for both the table and its unique index:

CREATE TABLE distributors ( did integer , name varchar( 40 ), UNIQUE(name) WITH ( fillfactor = 70 )) WITH ( fillfactor = 70);

Create table circles with an exclusion constraint that vents any two circles from overlapping:

CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH && ) );

Create table cinemas in tablespace diskvol1:

CREATE TABLE cinemas ( id serial, name text , location text ) TABLESPACE diskvol1;

Create a composite type and a typed table:

CREATE TYPE employee_type AS (name text , salary numeric); CREATE TABLE employees OF employee_type ( PRIMARY KEY (name), salary WITH OPTIONS DEFAULT 1000 );
 

原文链接:http://www.cnblogs.com/mchina/archive/2012/07/26/2601476.html
加载中
返回顶部
顶部