一、创建表

1.1 语法

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 ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_nameOF type_name [ ({ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]| table_constraint }[, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_namePARTITION OF parent_table [ ({ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]| table_constraint }[, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | 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 AS ( generation_expr ) STORED |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 referential_action ] [ ON UPDATE referential_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 referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]like_option 是：{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }partition_bound_spec 是:IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )TO ( { partition_bound_expr | 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 } ]

具体参数说明可参考：https://www.postgresql.org/docs/current/sql-createtable.html

1.2 常用示例

--创建表
CREATE TABLE test(id int, note varchar(20));
--创建表时带主键
CREATE TABLE test(id int PRIMARY KEY, note varchar(20));
--创建表时带复合主键
CREATE TABLE test(id1 int, id2 int, note varchar(20), CONSTRAINT pk_test PRIMARY KEY(id1,id2));
--创建表时带符合主键和唯一约束
CREATE TABLE test(id1 int, id2 int, id3 int, note varchar(20), CONSTRAINT pk_test PRIMARY KEY(id1,id2), CONSTRAINT uk_test_id3 UNIQUE(id3));
--创建表时带非空约束
CREATE TABLE test(id int NOT NULL, note varchar(20));
--创建表时带检查约束
CREATE TABLE child(name varchar(20), age int, note text, CONSTRAINT ck_child_age CHECK(age <18));
--创建表时带外键约束
CREATE TABLE class(class_no int PRIMARY KEY, class_name varchar(40));
CREATE TABLE student(student_no int PRIMARY KEY, student_name varchar(40), age int, class_no int REFERENCES class(class_no));
--创建表时指定默认值
CREATE TABLE student(no int, name varchar(20),age int DEFAULT 15);
--创建临时表
CREATE TEMPORARY/TEMP TABLE tmp_t1(id int PRIMARY KEY, note text);
--创建 UNLOGGED 表
CREATE UNLOGGED TABLE unlogged01(id int PRIMARY KEY, t text);

UNLOGGED表在使用上与普通表没有区别，仅仅在插入、删除、更新数据时不产生WAL日志，所以做这些DML操作的性能会更高。另外需要注意的是，数据库异常宕机时，UNLOGGED表的数据可能会丢失。

--复制表结构
CREATE TABLE baby (LIKE child);
CREATE TABLE baby AS SELECT * FROM child WITH NO DATA;
--复制表结构时带所有属性
CREATE TABLE baby (LIKE child INCLUDING ALL);

此处创建的表没有把源表列上的约束复制过来，第一种写法如果想完全复制源表列上的约束和其他信息，则需要加“INCLUDING”关键字，可用的“INCLUDES”选项如下：

INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
INCLUDING STORAGE
INCLUDING COMMENTS
INCLUDING ALL

二、修改表

--修改名表
ALTER TABLE table_name RENAME TO new_name;
--修改表所在模式
ALTER TABLE table_name SET SCHEMA new_schema;
--修改表所属 owner
ALTER TABLE table_name OWNER TO role_name;
--修改表所在表空间
ALTER TABLE table_name SET TABLESPACE tablespace_name;
--添加字段
ALTER TABLE table_name ADD column_name datatype;
--删除字段
ALTER TABLE table_name DROP column_name;
--修改字段类型
ALTER TABLE table_name ALTER column_name TYPE datatype;
--修改字段名
ALTER TABLE table_name RENAME column_name TO new_column_name;
--设置/删除字段非空约束
ALTER TABLE table_name ALTER column_name {SET|DROP} NOT NULL;
--设置字段默认值
ALTER TABLE table_name ALTER column_name SET DEFAULT expression;
--修改约束名
ALTER TABLE table_name RENAME CONSTRAINT constraint_name TO new_constraint_name;
--添加主键
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(id);
--添加唯一约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(id);
--添加检查约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(id > 100);
--添加外键约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(id) REFERENCES reftable(id);
--删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;