创建表

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

语法格式

• 创建表。

  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ]
  TABLE [ IF NOT EXISTS ] table_name
  ( { column_name data_type [ compress_mode ] [ COLLATE colla
  tion ] [ column_constraint [ ... ] ]
  | table_constraint
  | LIKE source_table [ like_option [...] ] }
  [, ... ])
  [ 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 ) |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED|
  GENERATED BY DEFAULT [ON NULL] AS IDENTITY [(sequence_optio
  ns[,...])]|
  UNIQUE 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 | INITIALL
  Y IMMEDIATE ]
  

• 其中表约束选项table_constrain为：

  [ CONSTRAINT constraint_name ]
  { CHECK ( expression ) |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  PARTIAL CLUSTER KEY ( column_name [, ... ] ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ..
  . ] ) ]
  [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UP
  DATE action ] }
  [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  

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

  { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
  

• 其中like选项like_option为：

  { INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STO
  RAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | 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 } ]
  

示例

• 创建简单的表。

  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