pg学习_基本表定义_创建表

创建表的语法结构highgo=# \h create tableCommand:     CREATE TABLEDescription: define a new tableSyntax:CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]    | table_constraint    | LIKE source_table [ like_option ... ] }    [, ... ]] )[ INHERITS ( parent_table [, ... ] ) ][ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ TABLESPACE tablespace_name ]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_name ]where column_constraint is:[ CONSTRAINT constraint_name ]{ NOT NULL |  NULL |  CHECK ( expression ) [ NO INHERIT ] |  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 ( expression ) [ NO INHERIT ] |  UNIQUE ( column_name [, ... ] ) index_parameters |  PRIMARY KEY ( column_name [, ... ] ) index_parameters |  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |  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_name ]exclude_element in an EXCLUDE constraint is:{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]Create table 向当前数据库中追加一个新的表，新表为执行本命令的用户所有。一个表的字段数目不能超过 1600 个，每一个字段的大小 不能超过 8192 字节(大对象字段除外)，用户表不能与系统表同名。创建表的简单示例highgo=# create table man(id int,name varchar);CREATE TABLEhighgo=# \d man           Table "public.man" Column |       Type        | Modifiers --------+-------------------+----------- id     | integer           |  name   | character varying | 通过脚本创建表[highgo@node1 ~]$ vi create_aman.sqlcreate table aman(id int,name varchar);执行脚本highgo=# \i /home/highgo/create_aman.sqlCREATE TABLE 显示表结构highgo=# \d aman          Table "public.aman" Column |       Type        | Modifiers --------+-------------------+----------- id     | integer           |  name   | character varying | 通过子查询生成表只创建表结构highgo=# create table bman highgo-# as highgo-# select * from aman where 1!=1;SELECT 0highgo=# select * from bman; id | name ----+------(0 rows)创建表结构并复制所有的表记录highgo=# create table cman highgo-# as highgo-# select * from aman;SELECT 1highgo=# select * from cman; id | name ----+------  1 | aman(1 row)