作者:鱼仔
博客首页: codeease.top (opens new window)
公众号:神秘的鱼仔

引言

表（Table）是数据存储的最基本单位，PostgreSQL中的表看上去和MySQL的表很像，但是他又多了很多的新特性，比如PGSQL中的表可以继承、可以做物化视图、可以做很多的约束、能实现分区、能实现行级别的安全。本篇博客会对PGSQL中表的基本特性以及特殊特性做个详细的介绍。

建表DDL

下面是一段表创建的SQL

CREATE TABLE table_name (
    column_name data_type [constraints],
    ...
) [options];

[constraints] 中可以写约束信息，比如通过正则表达式约束手机号字段只能输入符号约束的数据。

[options] 中可以写以下的内容

• INHERITS (parent_table)：继承父表。
• PARTITION BY：分区。
• WITH (storage_parameters)：存储参数，如 fillfactor（填充因子，默认 100，表示页填充百分比）。
• TABLESPACE tablespace_name：指定表空间。

一般主键ID都会自增，在PGSQL中可以这样写：

CREATE TABLE user (
    id SERIAL PRIMARY KEY,
    ...
);

这里的 SERIAL 表示自动递增的整数，PRIMARY KEY 表示主键

表的数据类型

下面这些是 PGSQL 中比较常用的数据类型：

• 数值：SMALLINT, INTEGER, BIGINT, NUMERIC(precision,scale), REAL, DOUBLE PRECISION。
• 字符：CHAR(n), VARCHAR(n), TEXT（无长度限制）。
• 日期/时间：DATE, TIME, TIMESTAMP, INTERVAL。
• 布尔：BOOLEAN。
• 数组：如 INTEGER[]。
• JSON：JSON, JSONB（二进制 JSON，支持索引）。

这里的JSON分成了JSON和JSONB，区别在于JSON会保存JSON的原始结构，包括空格、重复键等，但是无法制作索引，所以插入快、查询慢。

JSONB会解析为二进制，支持索引，插入相比JSON慢， 但是查询快。另外JSONB会忽略重复键，只保留最后一个，比如{"a":"1","a":"2"}会变成{"a":"2"}。

字段的约束

PGSQL可以给表中的每个字段设置约束，以下是PGSQL中针对表中字段的约束：

• PRIMARY KEY：唯一且非空。
• FOREIGN KEY：引用其他表，支持 ON DELETE/UPDATE（CASCADE, SET NULL 等）。
• UNIQUE：唯一约束。
• CHECK：条件检查。
• NOT NULL：非空。
• EXCLUSION：排除约束（用于重叠检查，如时间段）。

PRIMARY KEY

主键的使用和MySQL一样，表示某个字段唯一且非空：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

也可以设置联合主键，多个字段组成一个主键

CREATE TABLE products_2 (
    product_no integer,
    product_id integer,
    name text,
    price numeric,
    PRIMARY KEY(product_no,product_id)
);

FOREIGN KEY

通过外键约束，可以从数据库的层面保证外键的列和引用的列两者间的数据一致性。

下面是外键的标准写法

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

如果是单个字段也可以忽略掉字段，直接像下面这样写

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

还有一种做法是自引用外键，一张表中的某列引用这张表的另外一列，就像下面这样，这种自引用外键往往用在树形结构中。

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text
);

外键的删除

外键很好地对数据进行了约束，当我们需要删除一条主数据的时候，其他关联的记录要如何处理，在PGSQL中有多种自定义方式。

限制删除：通过 ON DELETE RESTRICT 关键字体现，根据下面的例子，如果departments中的要删除技术部，因为张三已经属于这个部门了， 在限制删除中无法被删除。

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  dept_id INT REFERENCES departments(id) ON DELETE RESTRICT
)
insert into departments (id,name) values (1,'技术部');
insert into employees(name,dept_id) values ('张三',1)

级联删除：通过 ON DELETE CASCADE 关键字体现。根据下面的例子，如果departments中的要删除技术部，则张三会被同步删除

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  dept_id INT REFERENCES departments(id) ON DELETE CASCADE
)
insert into departments (id,name) values (1,'技术部');
insert into employees(name,dept_id) values ('张三',1)

设置指定值：通过ON DELETE SET NULL或者ON DELETE SET DEFAULT 可以指定被删除后的另外两种操作，上面的例子以SET NULL为例，如果删除技术部，张三对应的部门就会变成NULL。

UNIQUE

唯一约束可以保证这个字段在表中的数据是唯一的，单个字段的约束写法如下

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

多个字段可以通过下面这种方式来写

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

创建唯一约束后，将自动在这个列或者整个组合列中添加B树索引。

在 SQL 标准中，NULL 不等于任何值（包括另一个 NULL），因此哪怕使用UNIQUE约束，如果某个字段都是NULL的话也不会报错。不过在某些场景中，NULL也只允许有一个，就可以使用UNIQUE NULLS NOT DISTINCT

CREATE TABLE demo (
    id int UNIQUE NULLS NOT DISTINCT
);

像上面这样配置后，如果连续插入两条：

INSERT INTO demo VALUES (NULL);
INSERT INTO demo VALUES (NULL);

第二条就会报错。

CHECK

检查约束是PGSQL中最常用的约束形式，通过Check约束可以保证字段按要求存入，下面是几个实际案例：

年龄需要大于 0

CREATE TABLE person (
    id   serial PRIMARY KEY,
    age  int CHECK (age >= 0)
);

性别只能是 M/F

CREATE TABLE student (
    id     serial PRIMARY KEY,
    gender char(1) CHECK (gender IN ('M','F'))
);

手机号必须符合规范

CREATE TABLE user_info (
    id    serial PRIMARY KEY,
    phone varchar(11) CHECK (phone ~ '^1[3-9][0-9]{9}$')
);

开始日期要小于结束日期

CREATE TABLE event (
    id          serial PRIMARY KEY,
    start_date  date,
    end_date    date CHECK (end_date > start_date)
);

NOT NULL

非空约束的使用和MySQL基本没有大的区别，约束字段不能为空。

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

EXCLUSION

排除约束，用法会更复杂些，用于控制“哪些行不能同时存在”，举例：

CREATE TABLE product_locale (
    id       serial PRIMARY KEY,
    product  int,
    lang     varchar(5),
    EXCLUDE USING btree (product WITH =, lang WITH =)
);

当一条新的数据插入时，会和所有的历史数据的product和lang做比较，如果同时相等，就触发了排除约束。这个知识点掌握即可。

参考文档

https://www.postgresql.org/docs/17/ddl-constraints.html (opens new window)