一、创建表

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_name
    OF 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_name
    PARTITION 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;

三、删除表

ALTER TABLE table_name [CASCADE];

CASCADE：自动删除依赖于该表的对象（例如视图）。

四、分区表

https://xiaosonggong.blog.csdn.net/article/details/123357556

脑子进水养啥鱼？

PostgreSql 表

一、创建表

1.1 语法

1.2 常用示例

二、修改表

三、删除表

四、分区表