数据定义
一、表基础
创建表
CREATE TABLE my_table (
first_column text,
second_column integer
);
删除表
DROP TABLE my_table;
如果删除不存在的表则会报错，可以使用DROP TABLE IF EXISTS变体来避免错误消息，但这并非标准SQL
DROP TABLE IF EXISTS my_table;
二、默认值
创建表时可以为列设置默认值，如果没有设置则默认为 null。
默认值可以为常量也可以是表达式：
CREATE TABLE my_table (
timestamp date DEFAULT CURRENT_TIMESTAMP,
product_no integer DEFAULT nextval('products_product_no_seq')
);
备注
nextval() 函数从一个序列对象提供连续的值
三、标识列
标识列是一种特殊列，它由一个隐式序列自动生成。在许多方面，标识列的行为类似于具有默认值的列。
标识列语法如下： GENERATED ... AS IDENTITY
CREATE TABLE my_table (
id1 bigint GENERATED ALWAYS AS IDENTITY,
id2 bigint GENERATED BY DEFAULT AS IDENTITY,
...,
);
信息
GENERATED BY DEFAULT AS IDENTITY：用户设置的值具有优先权
GENERATED ALWAYS AS IDENTITY：只有在 INSERT 语句指定了 OVERRIDING SYSTEM VALUE 时，才会接受用户指定的值
四、生成列
生成列是一种特殊的列，它总是根据其他列计算得出。
生成列有两种：存储型 和 虚拟型。
语法：
CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54)
);
生成列默认是虚拟类型。使用 VIRTUAL 或 STORED 关键字可以明确选择。
五、约束
SQL 允许您在列和表上定义约束。约束让您对表中的数据拥有您想要的控制权。如果用户尝试在违反约束的列中存储数据，将引发错误。即使值来自默认值定义，此规则也适用。
5.1 CHECK 约束
允许指定某一列中的值必须满足布尔（真值）表达式。
CREATE TABLE products (
product_no integer CHECK (product_no > 0),
# 为约束指定单独的名称。这可以使错误消息更清晰，并允许您在需要更改约束时引用它
price numeric CONSTRAINT positive_price CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
# 没有附加到任何列的，是表约束
CHECK (price > discounted_price AND price > 0)
);
5.2 NOT NULL 约束
指定列不得为 null 值。
CREATE TABLE products (
product_no integer NOT NULL,
name text CONSTRAINT products_name_not_null NOT NULL,
price numeric CHECK (price IS NOT NULL),
);
5.3 UNIQUE 约束
确保列或一组列中包含的数据在表中的所有行中都是唯一的。
CREATE TABLE products (
product_no1 integer UNIQUE,
product_no2 integer CONSTRAINT must_be_different UNIQUE,
# 默认 null 被认为不相等，所以可以有多行的 product_no3 设置为 null，使用 NULLS NOT DISTINCT 表示 只有一行的 product_no3 null
product_no3 integer UNIQUE NULLS NOT DISTINCT,
a integer,
c integer,
name text,
# a + c 的复合约束必须唯一
UNIQUE (a, c),
UNIQUE (name)
);
5.4 主键
表示列或一组列可以用作表中行的唯一标识符。这要求值既是唯一的又是 NOT NULL 的。
CREATE TABLE example (
b integer,
c integer,
PRIMARY KEY (b, c)
);
CREATE TABLE example (
a integer PRIMARY KEY,
);
5.5 外键
FOREIGN KEY 约束指定列或一组列中的值必须匹配另一个表中出现的行中的值。
# 产品表（被引用表）
CREATE TABLE products (
product_no interger PRIMARY KEY,
name text
);
# 订单表（引用表）
CREATE TABLE orders (
order_id interger PRIMARY KEY,
product_no interger REFERENCES products (product_no),
# 简写，不指定 products 的列，则默认使用 products 的主键
product_no interger REFERENCES products
);
自引用外键：
CREATE TABLE tree (
node_id integer PRIMARY KEY,
parent_id integer REFERENCES tree,
name text
);
实现多对多关系：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
外键删除时的约束
- ON DELETE NO ACTION：默认行为，允许删除被引用表中的删除操作继续进行
- ON DELETE RESTRICT：阻止删除被引用的行
- ON DELETE CASCADE：当被引用的行被删除时，引用它的行也将被自动删除
- ON DELETE SET NULL：当被引用的行被删除时，会将引用行中的引用列设置为 null
- ON DELETE SET DEFAULT：当被引用的行被删除时，会将引用行中的引用列设置为默认值
与 ON DELETE 类似，还有一个 ON UPDATE。
5.6 排斥约束
六、系统列
每个表都有几个由系统隐式定义的系统列。因此，这些名称不能用作用户定义的列名。
- tableoid：包含此行的表的 OID。此列对于选择来自分区表或继承层次结构的查询特别有用，因为如果没有它，很难知道一行来自哪个单独的表。可以
tableoid与oid列pg_class进行联接以获取表名。 - xmin：此行版本的插入事务的标识（事务 ID）。
- cmin：插入事务中的命令标识（从零开始）。
- xmax：删除事务的标识（事务 ID），对于未删除的行版本为零。可见的行版本中此列可能为非零。这通常表示删除事务尚未提交，或者尝试的删除已被回滚。
- cmax：删除事务中的命令标识，或零。
- ctid：行版本在表中的物理位置。