创建表

表是建立在数据库中的，在不同的数据库中可以存放相同的表。甚至可以通过使用模式在同一个数据库中创建相同名称的表。

语法格式

• 创建表。

  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
  ( { column_name data_type [ CHARACTER SET | CHARSET charset ]
  [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
  | table_constraint
  | LIKE source_table [ like_option [...] ] }
  [, ... ])
  [ AUTO_INCREMENT [ = ] value ]
  [ [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation
  ]
  [ INHERITS ( parent_table [, ... ] ) ]
  [ WITH ( {storage_parameter = value} [, ... ] ) ]
  [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
  [ COMPRESS | NOCOMPRESS ]
  [ TABLESPACE tablespace_name ];
  

• 其中列约束column_constraint为：

  [ CONSTRAINT [ constraint_name ] ]
  { NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) [STORED] |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [(sequence_options[,...])] |
  AUTO_INCREMENT |
  IDENTITY [ (seed , increment) ] |
  ON UPDATE update_expr |
  GENERATED BY DEFAULT [ON NULL] AS IDENTITY [(sequence_options[,...])]|
  UNIQUE [KEY] index_parameters |
  PRIMARY KEY index_parameters |
  ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
  [ ON DELETE action ] [ ON UPDATE action ] }
  [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  

• 其中表约束选项table_constrain为：

  [ CONSTRAINT [ constraint_name ] ]
  { CHECK ( expression ) |
  UNIQUE [ index_name ] [ USING method ] ( { { column_name | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_
  parameters [ VISIBLE | INVISIBLE ] |
  PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters [ VISIBLE | INVISIBL
  E ] |
  PARTIAL CLUSTER KEY ( column_name [, ... ] ) |
  FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
  [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
  [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  

• 其中列的压缩可选项compress_mode为：

  { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
  

• 其中like选项like_option为：

  { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES |
  STORAGE | COMMENTS |PARTITION | RELOPTIONS | ALL }
  

• 其中索引参数index_parameters为：

  [ WITH ( {storage_parameter = value} [, ... ] ) ]
  [ USING INDEX TABLESPACE tablespace_name ]
  

• 其中序列选项sequence_option可以为：

  [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE | NOMINVALUE]
  [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE]
  [ START [ WITH ] start ]
  [ CACHE cache ]
  [ [ NO ] CYCLE | NOCYCLE]
  [ OWNED BY { table_name.column_name | NONE } ]
  

• 其中range_distribution_rules为：

  [ ( SLICE name VALUES LESS THAN (expression | MAXVALUE [, ... ]) [DATANODE datanode_name]
  [, ... ] ) |
  ( SLICE name START (expression) END (expression) EVERY (expression) [DATANODE datanode_name]
  [, ... ] ) |
  SLICE REFERENCES table_name
  ]
  

• 其中list_distribution_rules为：

  [ ( SLICE name VALUES (expression [, ... ]) [DATANODE datanode_name]
  [, ... ] ) |
  ( SLICE name VALUES (DEFAULT) [DATANODE datanode_name] ) |
  SLICE REFERENCES table_name
  ]
  

示例

• 创建简单的表。

  CREATE TABLE public.warehouse_t1
  (
  W_WAREHOUSE_SK INTEGER NOT NULL,
  W_WAREHOUSE_ID CHAR(16) NOT NULL,
  W_WAREHOUSE_NAME VARCHAR(20) ,
  W_WAREHOUSE_SQ_FT INTEGER ,
  W_COUNTRY VARCHAR(20) ,
  W_GMT_OFFSET DECIMAL(5,2)
  );
  

• 创建表，并指定W_STATE字段的缺省值为GA。

  CREATE TABLE public.warehouse_t2
  (
  W_WAREHOUSE_SK INTEGER NOT NULL,
  W_WAREHOUSE_ID CHAR(16) NOT NULL,
  W_COUNTY VARCHAR(30) ,
  W_STATE CHAR(2) DEFAULT 'GA',
  W_ZIP CHAR(10) ,
  W_COUNTRY VARCHAR(20) ,
  W_GMT_OFFSET DECIMAL(5,2)
  );
  

• 创建一个带有70%填充因子的表。

  CREATE TABLE public.warehouse_t3
  (
  W_WAREHOUSE_SK INTEGER NOT NULL,
  W_COUNTRY VARCHAR(20) ,
  W_GMT_OFFSET DECIMAL(5,2)
  ) WITH(fillfactor=70);
  

• 创建表，并指定该表数据不写入预写日志。

  CREATE UNLOGGED TABLE public.warehouse_t4
  (
  W_WAREHOUSE_SK INTEGER NOT NULL,
  W_COUNTRY VARCHAR(20) ,
  W_GMT_OFFSET DECIMAL(5,2)
  );
  

• 创建表临时表。

  CREATE TEMPORARY TABLE warehouse_t5
  (
  W_WAREHOUSE_SK INTEGER NOT NULL,
  W_COUNTRY VARCHAR(20) ,
  W_GMT_OFFSET DECIMAL(5,2)
  );
  

• 创建表时，指定表空间PG_DEFAULT。

  CREATE TABLE public.warehouse_t6
  (
  W_ID INTEGER NOT NULL,
  W_NAME VARCHAR(20) ,
  W_MAIL VARCHAR(40)
  ) TABLESPACE PG_DEFAULT;
  

• 创建一个有主键约束的表。

  CREATE TABLE public.warehouse_t7
  (
  W_WAREHOUSE_SK INTEGER PRIMARY KEY,
  W_WAREHOUSE_ID CHAR(16) NOT NULL,
  W_COUNTRY VARCHAR(20) ,
  W_GMT_OFFSET DECIMAL(5,2)
  );
  

• 创建一个有复合主键约束的表。

  CREATE TABLE public.warehouse_t8
  (
  W_WAREHOUSE_SK INTEGER NOT NULL,
  W_WAREHOUSE_ID CHAR(16) NOT NULL,
  W_WAREHOUSE_NAME VARCHAR(20),
  W_COUNTRY VARCHAR(20),
  W_GMT_OFFSET DECIMAL(5,2),
  CONSTRAINT W_CSTR_KEY2 PRIMARY KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)
  );
  

• 定义一个检查列约束。

  CREATE TABLE public.warehouse_t9
  (
  W_WAREHOUSE_SK INTEGER CHECK (W_WAREHOUSE_SK > 0),
  W_WAREHOUSE_ID CHAR(16) NOT NULL,
  W_WAREHOUSE_NAME VARCHAR(20) CHECK (W_WAREHOUSE_NAME IS NOT NULL),
  W_WAREHOUSE_SQ_FT INTEGER ,
  W_GMT_OFFSET DECIMAL(5,2)
  );
  

• 向表中增加一个varchar列。

  ALTER TABLE public.warehouse_t1 ADD W_GOODS_CATEGORY varchar(30);
  

• 给表增加一个检查约束。

  ALTER TABLE public.warehouse_t2 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_WAREHOUSE_SK >10);
  

• 给一个已存在字段添加非空约束。

  ALTER TABLE public.warehouse_t3 ALTER COLUMN W_COUNTRY SET NOT NULL;
  

• 重命名已存在的表。

  ALTER TABLE public.warehouse_t4 RENAME TO newtab;
  

• 删除表。

  DROP TABLE public.warehouse_t1;
  DROP TABLE public.warehouse_t2;
  DROP TABLE public.warehouse_t3;
  

参考链接

CREATE TABLE、ALTER TABLE、DROP TABLE