pgsql的特性讲解

发表于 更新于 分类于 ， 阅读次数： Waline： 本文字数： 5.1k 阅读时长 ≈ 5 分钟

如果你已经会 MySQL 或写过后端 CRUD，换到 PostgreSQL 时真正要补的不是「SQL 是什么」，而是 PG 方言里多出来的写法：RETURNING、Identity、JSONB、数组、DISTINCT ON ……本文环境按 PostgreSQL 14+。

1. 用 Psql 跑 SQL

psql 是 PostgreSQL 自带的交互客户端，类似 MySQL 的 mysql 命令。

1.1 连上数据库

1
2
3
4
5
6
# 本地或容器内，按你的用户名、库名改
psql -U postgres -d postgres

# 已在 psql 里时：列出库、切换库
\l
\c mydb

没有本地 PG 时，可以用 Docker 起一个：

1
2
docker run --name pg-demo -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16
psql -h localhost -U postgres -d postgres

1.2 执行 SQL

1
2
3
4
5
-- 交互里直接写，分号结束
SELECT version();

-- 跑文件
\i /path/to/script.sql

1.3 看表结构

1
2
3
4
\dt              # 当前 schema 下的表
\d users         # 单表结构（列、索引、约束）
\x               # 切换「竖排 / 横排」显示，宽表时好用
\q               # 退出

如果你来自 MySQL：\dt ≈ SHOW TABLES；\d 表名 ≈ DESCRIBE 表名；数据库名用 \c 切换，不是 USE db。

2. SQL 核心速览

下面用同一张 users 表把「会 SQL 的人」需要的语法对齐一遍，不展开教程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id          bigint PRIMARY KEY,
  name        text NOT NULL,
  email       text UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'a@example.com');
UPDATE users SET name = 'Alice Lee' WHERE id = 1;
DELETE FROM users WHERE id = 1;

SELECT u.name, u.email FROM users u WHERE u.id = 1;
-- JOIN、GROUP BY、HAVING、子查询与标准 SQL 相同

BEGIN;
  INSERT INTO users (id, name) VALUES (2, 'Bob');
  -- 出错则 ROLLBACK; 否则：
COMMIT;

要点 bullet：

  • 类型：text 不限长字符串；时间推荐 timestamptz（带时区），别用 MySQL 式 DATETIME 思维硬套。
  • 标识符：未加引号的表名、列名会自动转小写；要保留大小写需双引号 "UserId"（尽量少用）。
  • 字符串：单引号 'hello'；双引号是标识符，不是字符串。

3. RETURNING

插入、更新、删除后，同一条语句把受影响的行返回出来，不必再 SELECT 一遍。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

INSERT INTO users (name) VALUES ('Alice')
RETURNING id, name;
--  id |  name
-- ----+-------
--   1 | Alice

UPDATE users SET name = 'Alice Lee' WHERE id = 1
RETURNING id, name;

DELETE FROM users WHERE id = 1
RETURNING id;

典型用途：拿自增主键、审计日志、ORM 里减少一次往返。

如果你来自 MySQL：没有等价关键字；常见做法是 INSERT 后再 SELECT LAST_INSERT_ID()，或驱动层 getGeneratedKeys()。PG 的 RETURNING 更直接，且在 UPDATE/DELETE 上同样可用。

4. 自增与 Identity

4.1 SERIAL（简写，仍常见）

1
2
3
4
CREATE TABLE t1 (
  id    serial PRIMARY KEY,   -- 等价于 integer + sequence + DEFAULT
  title text
);

serial / bigserial 本质是 integer + 独立 sequence，老项目、教程里很多。

4.2 GENERATED AS IDENTITY（PG 10+，推荐）

1
2
3
4
5
6
7
8
9
CREATE TABLE t2 (
  id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title text
);

-- 显式插 id 时允许（BY DEFAULT）
INSERT INTO t2 (id, title) VALUES (100, 'manual');

-- 若不允许手填 id，用 GENERATED ALWAYS AS IDENTITY

SQL 标准语法，和 Oracle、SQL Server 的 Identity 模型一致，新表优先用这个。

如果你来自 MySQL：AUTO_INCREMENT 列 ≈ GENERATED BY DEFAULT AS IDENTITY；PG 没有「表选项」里的 AUTO_INCREMENT，写在列定义上。

5. JSONB

jsonb 是二进制 JSON，可索引、可包含查询；Plain json 只存文本、每次解析，查询慢——除非有特殊理由，一律用 jsonb。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP TABLE IF EXISTS events;

CREATE TABLE events (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  kind       text NOT NULL,
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (kind, payload) VALUES
  ('click', '{"page": "/home", "tags": ["ui", "beta"]}');

-- ->  取 JSON 子节点（仍是 json/jsonb）
-- ->> 取文本
SELECT payload->>'page' AS page FROM events;
SELECT payload->'tags'->>0 AS first_tag FROM events;

-- 包含：左边 JSON 是否包含右边对象
SELECT * FROM events
WHERE payload @> '{"page": "/home"}';

-- 路径存在
SELECT * FROM events
WHERE payload ? 'page';

给 JSONB 建 GIN 索引（路径、包含查询会快很多）：

1
CREATE INDEX idx_events_payload ON events USING GIN (payload);

如果你来自 MySQL：MySQL 8 有 JSON 类型和 -> / ->>，概念接近；PG 的 @>、?、GIN 默认支持更成熟，复杂文档检索常少挂一个 Elasticsearch。

6. 数组

PG 列可以直接是数组，不必拆关联表或塞 JSON。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS articles;

CREATE TABLE articles (
  id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO articles (title, tags) VALUES
  ('PG 入门', ARRAY['sql', 'postgres']),
  ('Go 笔记', '{"go", "backend"}');   -- 字面量写法

-- 是否包含某个元素
SELECT title FROM articles WHERE 'sql' = ANY (tags);

-- 展开为多行
SELECT title, unnest(tags) AS tag FROM articles;

-- 数组长度
SELECT title, array_length(tags, 1) FROM articles;

标签、权限列表、多值枚举这类「一列多个值」，数组比 JSON 数组类型更轻、运算符更直接。

7. DISTINCT ON

每组按某排序取第一条，语法是 PG 独有（标准 SQL 没有直接等价）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DROP TABLE IF EXISTS scores;

CREATE TABLE scores (
  user_id  int NOT NULL,
  subject  text NOT NULL,
  score    int NOT NULL,
  exam_at  date NOT NULL
);

INSERT INTO scores VALUES
  (1, 'math', 90, '2026-01-01'),
  (1, 'math', 95, '2026-06-01'),
  (2, 'math', 80, '2026-01-01');

-- 每个 user_id 只保留 exam_at 最新的一行
SELECT DISTINCT ON (user_id)
  user_id, subject, score, exam_at
FROM scores
ORDER BY user_id, exam_at DESC;

DISTINCT ON 括号里的表达式必须先出现在 ORDER BY 最左侧，且顺序一致——这是最容易写错的地方。

如果你来自 MySQL：常用窗口函数 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY exam_at DESC) = 1 实现同样效果；PG 两种都能写，DISTINCT ON 往往更短。

8. 其他 PG 常用写法

零散但高频的差异，收在一节方便查。

8.1 大小写不敏感匹配：ILIKE

1
2
SELECT * FROM users WHERE name ILIKE '%alice%';
-- MySQL 对应：WHERE name LIKE '%alice%' COLLATE ... 或 LOWER(name) LIKE ...

8.2 类型转换：::

1
2
3
SELECT '42'::int;
SELECT now()::date;
-- 标准写法 CAST('42' AS int) 也行；PG 里 :: 更常见

8.3 COALESCE

1
2
SELECT COALESCE(nickname, name, '匿名') FROM users;
-- 与 MySQL 相同：返回参数里第一个非 NULL

8.4 LIMIT / OFFSET

1
2
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- 与 MySQL 相同；深分页大 offset 两边都慢，需要 keyset pagination

8.5 布尔类型

1
2
CREATE TABLE flags (ok boolean NOT NULL DEFAULT false);
SELECT * FROM flags WHERE ok = true;   -- 或直接 WHERE ok

MySQL 用 TINYINT(1) 冒充布尔；PG 有原生 boolean。