创建表

在当前数据库中创建一个新的空白表，该表由命令执行者所有。

语法格式

创建表语法如下：

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 [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

其中列约束column_constraint为：

[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
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 ]

其中表约束table_constraint为：

[ 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 ]

其中like选项like_option为：

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }

其中partition_bound_spec为：

IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

UNIQUE、PRIMARY KEY以及EXCLUDE约束中的index_parameters为：

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

EXCLUDE约束中的exclude_element为：

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

示例

创建表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 GENERATED BY DEFAULT AS IDENTITY,
 name   varchar(40) NOT NULL CHECK (name <> '')
);

创建有一个二维数组的表

CREATE TABLE array_int (
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上定义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
);

在表空间diskvol1中创建表cinemas

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

创建一个范围分区表

CREATE TABLE measurement (
logdate         date not null,
peaktemp        int,
unitsales       int
) PARTITION BY RANGE (logdate);

创建范围分区表的分区

CREATE TABLE measurement_y2016m07
PARTITION OF measurement (
unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

创建在分区键中具有多个列的范围分区表

CREATE TABLE measurement_year_month (
logdate         date not null,
peaktemp        int,
unitsales       int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));

创建列表分区表

CREATE TABLE cities (
city_id      bigserial not null,
name         text not null,
population   bigint
) PARTITION BY LIST (left(lower(name), 1));

创建列表分区表的分区

CREATE TABLE cities_ab
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');

创建哈希分区表

CREATE TABLE orders (
order_id     bigint not null,
cust_id      bigint not null,
status       text
) PARTITION BY HASH (order_id);

创建哈希分区表的分区

CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

创建默认分区

CREATE TABLE cities_partdef
PARTITION OF cities DEFAULT;

VastbaseE100

创建表

弹框头部