学习的是https://www.bilibili.com/video/BV1HyWxzQE7K?spm_id_from=333.788.player.switch&vd_source=0ea1552687da3b191a212f42633ae406提供的教学视频，PSQL的12版本。基本内容应该不会差太多，如果有区别，我会直接记下最新操作的笔记。

1. Postgresql和MySQL的对比

2. postgresql安装

安装过程省略，推荐在linux上进行安装使用，并且Linux最好是最小安装，不要带GUI界面的那种。在安装完成之后，由于postgresql不推荐使用root管理，它会默认给你创建一个用户：postgres。所以在玩PGSQL之前，要先切换到postgres用户。

xxxxxxxxxx
8
1
# 切换到 postgres 用户，密码 !@#qaz12 。su命令是 switch user的简写。
2
su postgres
3
​
4
# 进入到postgresql的客户端命令行界面
5
psql
6
​
7
#查看有哪些库，在命令行中输入
8
\l

3. postgresql配置

postgresql的主要配置放在数据目录（data/）下，postgresql.conf和pg_hba.conf。

/etc/postgresql/14/main/pg_hba.conf

/etc/postgresql/14/main/postgresql.conf

3.1 远程连接配置

主用户：

xxxxxxxxxx
1
1
sudo nano /etc/postgresql/14/main/pg_hba.conf

然后在文件底部加入，这表示允许任意地址的所有用户连接所有数据库。修改后保存文件。

xxxxxxxxxx
1
1
host    all    all    0.0.0.0/0    md5

然后编辑：

xxxxxxxxxx
1
1
sudo nano /etc/postgresql/14/main/postgresql.conf

将里面的listen_addresses改为*：

xxxxxxxxxx
1
1
listen_addresses = '*'

保存文件后重启postgresql：

xxxxxxxxxx
1
1
sudo systemctl restart postgresql

3.2 日志配置

为什么要配置日志？

✅ 1. SQL 报错很难查

例如你的应用报：

xxxxxxxxxx
1
1
syntax error at or near “FROM”

但你不知道是哪一条 SQL 出错。 开启 SQL 日志后，你能看到完整 SQL。

✅ 2. 性能调优离不开日志

你可以找出：

• 慢 SQL（执行 1s 以上的操作）
• 锁阻塞（哪个 SQL 卡住了）
• 全表扫描
• 数据库连接数满了
• 自动 VACUUM 或 autovacuum 卡住

所有这些都必须从日志判断。

✅ 3. 安全审计

可以记录：

• 谁登录数据库
• 什么 IP
• 执行了哪些管理指令
• 失败的登录尝试

这是企业应用、线上系统必备的。

⚠️ 结论：不配置日志 = 出问题没地方查

xxxxxxxxxx
1
1
sudo nano /etc/postgresql/14/main/postgresql.conf

按照下面来配置即可：

postgresql默认情况下，只保存7天的日志，循环覆盖。下面是参数说明：

4. 基操用户操作

进入到psql命令行之后。

\help，查看数据库级别的命令：

\help 具体的命令，查看具体命令的使用方法，比如说\help create user：

\?，查看服务级别的命令，按q退出这个界面：

\q，退出psql命令行：

\du，查看全部用户：

下面是一些实际操作：

创建一个超级管理员用户。

xxxxxxxxxx
1
1
create user root with SUPERUSER PASSWORD 'root';

为什么这里的回显是create role呢？我明明是创建了用户啊，原因是从 PostgreSQL 8.1 开始，CREATE USER 只是 CREATE ROLE 的语法糖（别名）。

换句话说：

xxxxxxxxxx
1
1
CREATE USER = CREATE ROLE WITH LOGIN

只是默认带上 LOGIN（可以登录数据库） 属性而已。

注意，数据库的用户和我认为的前端网站的登录用户，对于数据库的操作是完全不同的概念。

数据库用户是数据库内部的账号，用来：

• 连接数据库（LOGIN）
• 读/写/删/改数据
• 管理数据库对象（表、索引、schema）
• 控制权限与安全

📌 DB 用户是给后端/服务用的，不是给网站会员用的。下节课说的用户权限，指的是DB用户的权限，指的是可以直接连接这个数据库后的操作权限。

 

网站用户是你的网站/应用里的普通用户：

• 注册 / 登录
• 拥有自己的账号资料
• 角色：普通用户 / VIP / 管理员
• 权限：浏览、下单、评论、点赞等
• 存在于你数据库的某个业务表中，比如 users 表里面，加上几个关联表。
• 权限由应用层控制，比如说判断用户的角色，角色也有roles表，然后根据角色来判断是否能够增删改查。

项目	数据库用户（DB User）	网站用户（App User）
定义	PostgreSQL 内部的账号	存在 users 表里的业务账号
用于	系统访问数据库	用户访问网站
数量	很少	很多
控制权限	SQL 权限	应用代码
不能互相替代	✔	✔

5. 基操权限操作和小任务

PGSQL的逻辑结构

这部分的内容还是蛮重要的，因为数据安全的重要性不言而喻。但是这部分先不用管，因为命令很简单，到时候搜索即可。

postgresql的逻辑结构：

我们用最直观的方式，把 PostgreSQL 的逻辑结构从“大到小”一层一层讲清楚，你看完就彻底明白了。PostgreSQL 逻辑结构全景图（从大到小）

层级	名称	说明	举个生活中的比喻
1. 整个 PostgreSQL 服务	集群（Cluster）	你启动一个 PostgreSQL 服务（一个端口 5432），它就是一个集群	一整个学校
2. 集群下面有很多	数据库（Database）	集群里可以创建很多个数据库，彼此数据完全隔离	学校里有许多独立的教学楼（文科楼、理科楼）
3. 每个数据库里面有	Schema（模式）	一个数据库里又可以再划分很多 schema，相当于子命名空间	每栋教学楼里有很多教室（schema）
4. Schema 里面放	对象（表、视图、函数、序列等）	真正的表、视图、函数都放在某个 schema 里	教室里放课表、黑板、学生（表、数据）

下面用一张图帮你记住（文字版）：

xxxxxxxxxx
17
1
一个 PostgreSQL 实例（Cluster）
2
├── 数据库1：company_db
3
│   ├── schema: public         ← 默认就有的
4
│   │   ├── 表: users
5
│   │   ├── 表: orders
6
│   │   └── 视图: vip_customers
7
│   ├── schema: hr              ← 自己建的
8
│   │   └── 表: employees
9
│   └── schema: finance
10
│       └── 表: salary
11
│
12
├── 数据库2：test_db
13
│   ├── schema: public
14
│   │   └── 表: test_table
15
│   └── schema: dev
16
│
17
└── 数据库3：postgres（系统默认数据库）

每个层级最核心的要点

层级	关键点（你一定要记住）
Cluster	一个 PostgreSQL 服务进程（一个端口）就是一个集群，全局共享一套用户（角色）和 tablespace
Database	数据完全隔离！不同数据库之间的表、函数同名也不冲突；连接时必须指定连哪个数据库（psql -d company_db）
Schema	只是命名空间，不隔离数据！同一个数据库里不同 schema 的表可以互相 join；全路径是 database.schema.table
Table 等对象	真正存放数据的地方，必须属于某个 schema

实际工作中最常用的路径写法当你写 SQL 时，完整对象名是这样写的：

xxxxxxxxxx
4
1
-- 格式：  数据库名 . schema名 . 对象名
2
SELECT * FROM company_db.public.users;        -- 跨数据库要用 dblink 扩展
3
SELECT * FROM public.users;                   -- 省略数据库（当前连接的数据库）
4
SELECT * FROM hr.employees;                   -- 省略 public，用自己的 schema

再举一个真实项目的典型结构（强烈推荐你也这么用）

xxxxxxxxxx
12
1
-- 1. 创建业务数据库
2
CREATE DATABASE shop;
3
​
4
-- 2. 连接到 shop 数据库后，创建几个 schema
5
CREATE SCHEMA auth;      -- 存放跟认证相关的表
6
CREATE SCHEMA order_sys; -- 订单系统
7
CREATE SCHEMA payment;   -- 支付相关
8
CREATE SCHEMA report;    -- 报表、统计视图
9
​
10
-- 3. 建表时都指定 schema
11
CREATE TABLE order_sys.orders (...);
12
CREATE TABLE payment.records (...);

这样做的好处：

• 表多了也不乱，几百张表一眼就能看出属于哪个模块
• 不同团队可以分别管理自己的 schema
• 以后要迁移某个模块的数据，只需要 dump 对应 schema 就行

总结一句话记住了：PostgreSQL 逻辑结构只有 4 层： 集群 → 多个数据库（完全隔离） → 每个数据库里多个 schema（命名空间） → schema 里放表、视图、函数等。相当于： 一个 PostgreSQL 服务 = 一所大学 多个数据库 = 多栋教学楼（楼和楼之间完全不联通） 多个 schema = 每栋楼里的不同教室分区 表 = 教室里的课桌和学生搞清楚这四层关系后，你再看任何 PostgreSQL 项目结构都不会懵了！

下面是在一个项目中使用postgresql的过程，从中可以看到postgresql的各种概念的使用。

1. 本地/服务器上创建数据库、用户
2. 推荐的数据库 & schema 结构
3. 初始化表（迁移方式）
4. Next.js 14（App Router）项目里怎么安全、优雅地连接和操作 PostgreSQL
5. 生产环境部署注意事项（Vercel / 自建服务器）

一、PostgreSQL 端：完整建库建用户脚本（直接拷贝执行）

xxxxxxxxxx
31
1
-- 1. 登录 postgres 超级用户（本地通常是 postgres 或你的系统用户）
2
sudo -u postgres psql
3
​
4
-- 2. 创建专门给这个 Next.js 项目用的数据库用户（建议不直接用 postgres）
5
CREATE ROLE myapp_user WITH LOGIN PASSWORD 'your_strong_password_here';
6
​
7
-- 3. 创建业务数据库，推荐让应用用户直接做 owner（最省事权限最大）
8
CREATE DATABASE myapp_prod
9
    OWNER myapp_user
10
    ENCODING 'UTF8'
11
    LC_COLLATE 'en_US.UTF-8'
12
    LC_CTYPE 'en_US.UTF-8'
13
    TEMPLATE template0;
14
​
15
-- 4. 连接到刚创建的数据库
16
\c myapp_prod
17
​
18
-- 5. 创建常用的 schema（强烈推荐按模块分），如果不创建，默认是放在public这个schema里面
19
CREATE SCHEMA auth      AUTHORIZATION myapp_user;
20
CREATE SCHEMA core      AUTHORIZATION myapp_user;
21
CREATE SCHEMA billing   AUTHORIZATION myapp_user;
22
CREATE SCHEMA analytics AUTHORIZATION myapp_user;
23
​
24
-- 6. 给 public schema 收回默认权限（安全最佳实践，从 PG13 起默认已经这样了）
25
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
26
-- 如果你完全不用 public，可以直接删掉里面的默认权限
27
​
28
-- 7. （可选）创建扩展，很多项目都会用到
29
CREATE EXTENSION IF EXISTS "uuid-ossp";     -- uuid_generate_v4()
30
CREATE EXTENSION IF EXISTS "pgcrypto";      -- crypt(), gen_salt()
31
CREATE EXTENSION IF EXISTS "citext";        -- 不区分大小写的文本类型

二、推荐的表结构示例（用迁移工具管理，后面会讲）

xxxxxxxxxx
20
1
-- 示例：users 表放在 auth schema
2
CREATE TABLE auth.users (
3
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
4
    email         CITEXT UNIQUE NOT NULL,
5
    email_verified BOOLEAN DEFAULT false,
6
    password_hash TEXT,
7
    name          TEXT,
8
    avatar_url    TEXT,
9
    created_at    TIMESTAMPTZ DEFAULT NOW() NOT NULL,
10
    updated_at    TIMESTAMPTZ DEFAULT NOW() NOT NULL
11
);
12
​
13
-- 示例：orders 表放在 core schema
14
CREATE TABLE core.orders (
15
    id            BIGSERIAL PRIMARY KEY,
16
    user_id       UUID NOT NULL REFERENCES auth.users(id),
17
    status        TEXT NOT NULL DEFAULT 'pending',
18
    total_cents   INTEGER NOT NULL,
19
    created_at    TIMESTAMPTZ DEFAULT NOW()
20
);

三、Next.js 项目端完整配置（Next.js 14 App Router 推荐做法 2025 年版）1. 安装依赖（2025 年最推荐组合）

xxxxxxxxxx
3
1
npm install prisma @prisma/client
2
# 或者如果你不想用 Prisma，也可以用
3
npm install pg drizzle-orm @neondatabase/serverless

下面分别给出 Prisma（最推荐） 和 Drizzle（轻量） 两套方案，任选其一。方案 A：Prisma（99% 新项目选这个）

xxxxxxxxxx
1
1
npx prisma init --datasource-provider postgresql

1. .env 文件（本地和生产都放这里）

xxxxxxxxxx
5
1
# 本地开发
2
DATABASE_URL="postgresql://myapp_user:your_strong_password_here@localhost:5432/myapp_prod?schema=public"
3
​
4
# 生产（推荐用 Neon / Supabase / Railway / AWS RDS）
5
DATABASE_URL="postgresql://myapp_user:xxxxxx@ep-xxxxx.us-east-2.aws.neon.tech/myapp_prod?sslmode=require"

1. prisma/schema.prisma（关键：多 schema 配置）

xxxxxxxxxx
35
1
generator client {
2
  provider = "prisma-client-js"
3
}
4
​
5
datasource db {
6
  provider = "postgresql"
7
  url      = env("DATABASE_URL")
8
  // 关键！！告诉 Prisma 你用了多个 schema
9
  schemas  = ["auth", "core", "billing", "analytics"]
10
}
11
​
12
model User {
13
  id             String    @id @default(uuid()) @db.Uuid
14
  email          String    @unique @db.Citext
15
  emailVerified  Boolean   @default(false) @map("email_verified")
16
  passwordHash   String?   @map("password_hash")
17
  name           String?
18
  avatarUrl      String?   @map("avatar_url")
19
  createdAt      DateTime  @default(now()) @map("created_at")
20
  updatedAt      DateTime  @default(now()) @updatedAt @map("updated_at")
21
​
22
  @@schema("auth")
23
}
24
​
25
model Order {
26
  id         BigInt   @id @default(autoincrement())
27
  userId     String   @db.Uuid
28
  status     String   @default("pending")
29
  totalCents Int      @map("total_cents")
30
  createdAt  DateTime @default(now()) @map("created_at")
31
​
32
  user User @relation(fields: [userId], references: [id])
33
​
34
  @@schema("core")
35
}

1. 迁移命令（每次改 schema 后执行）

xxxxxxxxxx
8
1
# 开发时实时生成 client
2
npx prisma generate
3
​
4
# 创建并执行迁移
5
npx prisma migrate dev --name init
6
​
7
# 生产环境
8
npx prisma migrate deploy

1. 在 Next.js 中使用（lib/prisma.ts）

xxxxxxxxxx
13
1
import { PrismaClient } from '@prisma/client'
2
​
3
const globalForPrisma = global as unknown as { prisma: PrismaClient }
4
​
5
export const prisma =
6
  globalForPrisma.prisma ||
7
  new PrismaClient({
8
    log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'],
9
  })
10
​
11
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma
12
​
13
export default prisma

1. 在 Server Component / Route Handler / Server Action 里随便用

xxxxxxxxxx
9
1
// app/api/users/route.ts
2
import prisma from '@/lib/prisma'
3
​
4
export async function GET() {
5
  const users = await prisma.user.findMany({
6
    select: { id: true, email: true, name: true }
7
  })
8
  return Response.json(users)
9
}

四、生产部署最重要几件事（不能忘！）

项目	推荐做法
数据库托管	Neon.tech（免费层超强） / Supabase Postgres / Railway / AWS RDS
环境变量	Vercel → Project Settings → Environment Variables 里加 DATABASE_URL
SSL	生产必须强制 sslmode=require
连接池	Vercel/Serverless 环境必须用 PgBouncer 或 Neon 的 serverless driver
Prisma 加速	推荐开启 Prisma Accelerate（连接池 + 边缘缓存）
敏感密码	永远不要写死，用 Vercel / Railway 的加密环境变量
备份	Neon 自动 PITR，Supabase 也有自动备份

五、完整一步步操作清单（直接照着做就行）

1. 装好 PostgreSQL（本地开发）或注册 Neon/Supabase（推荐）
2. 执行上面第一段 SQL 创建用户、数据库、schema
3. 创建 Next.js 项目 npx create-next-app@latest myapp --ts
4. 装 Prisma → prisma init → 配置 schema.prisma
5. 写完模型 → npx prisma migrate dev --name init
6. 开发中使用，部署时只跑 npx prisma migrate deploy
7. 把 DATABASE_URL 填到 Vercel 环境变量 → 部署

6. pgAdmin图形化界面连接和基本操作

连接成功之后，可以打开命令行：

打开查询工具：

显示中文界面

7. 数据类型

90%情况下，只需要关注数字、字符串、时间这三种类型即可。

总结：你日常只需要记住这几行就行

xxxxxxxxxx
12
1
id            UUID PRIMARY KEY DEFAULT uuid_generate_v4()
2
id            BIGSERIAL PRIMARY KEY
3
email         CITEXT UNIQUE NOT NULL
4
password_hash TEXT
5
amount        NUMERIC(12,2)
6
total_cents   INTEGER
7
content       TEXT
8
metadata      JSONB DEFAULT '{}'
9
tags          TEXT[] DEFAULT '{}'
10
created_at    TIMESTAMPTZ DEFAULT NOW()
11
updated_at    TIMESTAMPTZ DEFAULT NOW()
12
deleted_at    TIMESTAMPTZ

2025 年实际项目中 99% 情况你只需要记住下面这 10 个就够了（生产推荐组合）

枚举类型怎么写（推荐替代方案）不要用 PostgreSQL 原生 enum（改起来很麻烦），改用 check 约束：

xxxxxxxxxx
6
1
CREATE TABLE core.orders (
2
  id         BIGSERIAL PRIMARY KEY,
3
  status     TEXT NOT NULL DEFAULT 'pending' CHECK (status IN (
4
               'pending', 'paid', 'shipped', 'completed', 'cancelled', 'refunded'
5
             ))
6
);

详细说明：

8. PGSQL操作

8.1 单引号和双引号

单引号

在PGSQL中，写SQL语句时，单引号 ('...') 用于界定 字符串常量 (String Literals)，也就是 SQL 语句中用作数据的文本值。

核心规则：

• 界定数据： 任何作为数据插入、更新或比较的文本，都必须用单引号括起来。
• 数据类型： 只要是用单引号括起来的值，PostgreSQL 会尝试将其解析为适合该上下文的数据类型（如 VARCHAR, TEXT, DATE, TIMESTAMP）。

示例：

xxxxxxxxxx
8
1
-- 示例 1: 字符串数据
2
INSERT INTO products (name) VALUES ('笔记本电脑'); 
3
​
4
-- 示例 2: 日期/时间数据 (虽然看起来是文本，但会被解析为日期)
5
SELECT * FROM orders WHERE order_date = '2025-12-01';
6
​
7
-- 示例 3: 在 SELECT 语句中返回一个固定的文本
8
SELECT 'Hello World' AS message;

双引号

双引号 ("...") 用于界定 标识符 (Identifiers)，即数据库对象（表名、列名、模式名、函数名等）的名称。

核心规则：

• 区分大小写： 这是双引号最关键的作用。在 PostgreSQL 中，如果一个标识符没有被双引号括起来，它会被自动转换为 小写。如果使用双引号，则名称会严格区分大小写，并保留你提供的原样。
• 允许使用非法字符： 双引号允许你在标识符中使用空格、特殊字符或 SQL 保留字。

示例：

A. 默认行为 (不使用双引号)

xxxxxxxxxx
9
1
CREATE TABLE MyTable (
2
    FirstName VARCHAR(50)
3
);
4
​
5
-- 实际创建的表名是: mytable
6
-- 实际创建的列名是: firstname
7
​
8
-- 查询时必须使用小写 (或不使用双引号)
9
SELECT firstname FROM mytable; 

B. 使用双引号 (强制区分大小写)

xxxxxxxxxx
7
1
CREATE TABLE "MyTable" (
2
    "First Name" VARCHAR(50), -- 允许空格
3
    "ORDER" INTEGER           -- 允许使用 SQL 保留字
4
);
5
​
6
-- 查询时必须精确使用双引号和原先的大小写/空格
7
SELECT "First Name" FROM "MyTable" WHERE "ORDER" > 10;

最佳实践： 强烈建议不要在标识符中使用双引号，除非你确实需要使用空格或保留字。遵循小写和下划线的命名约定 (e.g., user_account) 可以使你的 SQL 代码更简洁、更具可移植性。

8.2 数据类型转换

方式一：只需要在值的前面，添加上具体的数据类型即可

方式二：在具体值后面，添加上 ::类型 来指定

完整写法：

8.3 布尔类型

有三种值：true、false、null。

灵活的输入值 (Valid Literal Values)

这是 PostgreSQL 与其他数据库最大的不同点之一。它非常宽容，除了标准的 TRUE/FALSE，还接受多种字符串表示形式（不区分大小写）。

注意： 使用字符串形式（如 'yes'）时，必须加单引号；使用关键字 TRUE 或 FALSE 时不需要引号。

如果要使用字符串或者数字写法来当作布尔值，select的时候要进行转换才能称为boolean类型

xxxxxxxxxx
8
1
-- 整数转布尔 (非0为True，0为False，但在PG中仅0和1能直接转，其他需逻辑判断)
2
SELECT 1::BOOLEAN;  -- 结果: true
3
SELECT 0::BOOLEAN;  -- 结果: false
4
SELECT 'yes'::BOOLEAN; -- 结果: true
5
​
6
-- 布尔转整数 (需要显式转换)
7
SELECT (TRUE)::INTEGER;  -- 结果: 1
8
SELECT (FALSE)::INTEGER; -- 结果: 0

推荐写法 (简洁)

不需要写 = TRUE 或 = FALSE。

xxxxxxxxxx
5
1
-- 查找活跃用户 (is_active 为真)
2
SELECT * FROM users WHERE is_active;
3
​
4
-- 查找非活跃用户 (is_active 为假)
5
SELECT * FROM users WHERE NOT is_active;

处理 NULL 值 (三值逻辑)

这是布尔类型最容易出错的地方。NULL 既不是 TRUE 也不是 FALSE。

如果你想查询“所有未明确被禁止”的用户（即包含 TRUE 和 NULL，但不包含 FALSE），你需要使用 IS NOT 语法：

xxxxxxxxxx
5
1
-- 包含 TRUE 和 NULL 的行
2
SELECT * FROM users WHERE is_active IS NOT FALSE;
3
​
4
-- 包含 FALSE 和 NULL 的行
5
SELECT * FROM users WHERE is_active IS NOT TRUE;

boolean类型在做and or的逻辑操作时，结果有哪些

1. 逻辑与 (AND) 的结果

AND 操作只有在所有输入都为 TRUE 时，结果才为 TRUE。只要有任何一个操作数为 FALSE，结果立即为 FALSE。

2. 逻辑或 (OR) 的结果

OR 操作只要有一个输入为 TRUE，结果就为 TRUE。只有当所有输入都为 FALSE 时，结果才为 FALSE。

3. 逻辑非 (NOT) 的结果

NOT 操作就是对操作数取反。

4. 总结与在 WHERE 子句中的应用

理解三值逻辑最重要的一点，在于它如何影响数据库查询中的 WHERE 子句：

WHERE 子句只会返回那些其条件表达式计算结果为 TRUE 的行。

这意味着：

1. 如果条件计算结果是 TRUE，则该行被返回。
2. 如果条件计算结果是 FALSE，则该行被过滤。
3. 如果条件计算结果是 NULL (未知)，则该行也被过滤。

最佳实践建议

1. 命名规范： 给布尔字段命名前加上 is_、has_ 或 can_ 等前缀，这样代码的可读性极高。

   • ✅ is_visible, has_permission, can_login
   • ❌ visible, permission, login (容易被误认为是名词或字符串)

2. 默认值： 尽量在建表时给布尔字段设置 DEFAULT 值（通常是 FALSE）。这可以避免在业务逻辑中处理棘手的 NULL 值。

   xxxxxxxxxx
   1
   1
   is_deleted BOOLEAN NOT NULL DEFAULT FALSE
   

3. 避免使用 CHAR(1) 或 INT： 虽然可以用 0/1 或 'Y'/'N' 来模拟，但使用原生的 BOOLEAN 类型存储效率更高，且语义更明确，能防止无效数据的插入（例如防止插入 'X'）。

8.4 数值类型

8.4.1 整型

类型别名：

8.4.2 浮点型

就关注两种类型（本质上就只有一个）

• decimal(n, m)：本质就是numeric类型，PGSQL会帮你转换为numeric类型
• numeric(n, m)：PGSQL本质的浮点类型

针对浮点类型的数据，就使用 numeric ，不用考虑其它。

语法：NUMERIC(p, s)

NUMERIC 允许您指定精度和标度：

1. $p$ (Precision，精度)： 数字的总位数（小数点左右）。
2. $s$ (Scale，标度)： 小数点右侧的位数。

示例：

xxxxxxxxxx
7
1
-- NUMERIC(5, 2)
2
-- 总共 5 位数字，其中 2 位在小数点后。
3
-- 范围大致是 -999.99 到 999.99
4
CREATE TABLE finance (
5
    price NUMERIC(5, 2), 
6
    tax_rate DECIMAL(4, 3) -- DECIMAL 是 NUMERIC 的别名
7
);

8.4.3 序列

序列是 PostgreSQL 用于生成唯一、有序、自动递增（或递减）的整数的数据库对象。它们是实现自动增长主键的基石。MySQL中的主键自增，是基于auto_increment来实现的，MySQL里面没有序列对象。PGSQL和Oracle十分相似，支持序列：sequence。PGSQL是没有auto_increment的。

序列的定义和核心用途

A. 定义 (What is a Sequence?)

序列是一个独立的数据库对象，它不属于任何表，但可以被任何表或查询引用。它的主要作用是维护一个计数器，并保证在任何时间点，只要调用它，就能返回一个唯一且原子性递增的数值。

B. 核心用途

序列最主要的应用就是作为表的主键 (Primary Key) 生成器，确保每条新插入的记录都有一个独一无二的 ID。

序列的正常构建方式

A. 创建序列

默认情况下，sequence的起始值是0，最大值9223372036854775807，每次nextval递增1。

xxxxxxxxxx
1
1
CREATE SEQUENCE table_id_seq;

创建之后可以看到：

B. 主要的操作函数

C. 应用-当作表的主键生成器

xxxxxxxxxx
4
1
CREATE TABLE xxx(
2
    id bigint DEFAULT nextval('table_id_seq'),
3
    name varchar(16)
4
);

然后执行多次插入语句，就可以看到id是自增的。

xxxxxxxxxx
1
1
INSERT INTO xxx (name) VALUES ('xxx');

序列的简单构建方式

序列的正常构建方式，需要先专门构建一个序列表。PGSQL提供了序列这种语法糖，可以在声明表结构的时候，直接指定序列的类型即可。

SERIAL 的本质：自动化脚本

SERIAL 本身并不是一种数据类型。它是一个方便的快捷方式（或称“语法糖”），它指示 PostgreSQL 在您创建表时，自动执行三个独立的 SQL 步骤，从而将一列变成自动递增的唯一标识符。

当您执行 CREATE TABLE t (id SERIAL); 时，数据库内部实际执行了以下操作：

核心优势： 您只需要写一行 id SERIAL，PostgreSQL 就为您完成了复杂的序列创建、关联和约束设置工作。

三种 SERIAL 变体

最佳实践选择：

• 默认选择： SERIAL (INTEGER) 适用于绝大多数应用。
• 高流量/大表： 如果您的表预计会存储超过 20 亿行，或者您想最大化安全范围，请使用 BIGSERIAL。这是很多现代大型系统的推荐。

SERIAL 的重要特性：所有权与依赖性

使用 SERIAL 创建的序列对象与手动创建的序列对象有一个关键区别：所有权 (Ownership)。

A. 自动所有权

当您使用 SERIAL 创建一列时，PostgreSQL 会自动将序列的所有权归属于该表列。

xxxxxxxxxx
4
1
CREATE TABLE users (
2
    user_id BIGSERIAL PRIMARY KEY,
3
    username VARCHAR(50)
4
);

在这种情况下，序列 users_user_id_seq 归属于 users 表的 user_id 列。

B. 级联删除 (Dependency)

由于所有权关系的存在，序列具有强大的依赖性：

当您删除（DROP）包含 SERIAL 列的表时，PostgreSQL 会自动删除关联的序列对象。我试过了，是真的。

这使得管理非常方便，不会在数据库中留下无用的序列垃圾。如果序列是手动创建的，您必须手动删除序列（DROP SEQUENCE）。

C. 序列名称查找

如果您需要检查或修改序列的属性（如手动重置起始值），您需要知道它的名字。PostgreSQL 默认的命名规则是：[表名]_[列名]_seq。

示例：重置序列值

假设您的 users 表因为数据导入导致序列号混乱，需要从 10000 开始：

xxxxxxxxxx
5
1
-- 1. 查找序列名称 (通常是 users_user_id_seq)
2
​
3
-- 2. 使用 setval() 函数重置序列的当前值
4
SELECT setval('users_user_id_seq', 10000, true); 
5
-- 第三个参数 'true' 表示下一个 nextval() 调用将返回 10001

简单构建方式和正常构建方式的区别

8.4.4 数值类型的常见操作

1. 基础数学运算符 (Arithmetic Operators)

这是最常用的一组操作，遵循标准的数学规则。

⚠️ 注意整型除法：

当使用两个整型（INTEGER 或 BIGINT）进行 / 除法时，结果仍然是整型，任何小数部分都会被截断 (truncated)，而不是四舍五入。

如果需要精确的浮点数结果，请确保至少有一个操作数是浮点类型：

SELECT 10.0 / 3; $\rightarrow$ $3.333333...$

2. 比较和逻辑操作 (Comparison and Logic)

这些操作返回一个布尔值 (TRUE, FALSE, 或 NULL)，主要用于 WHERE 或 HAVING 子句进行过滤。

3. 聚合函数 (Aggregate Functions)

聚合函数用于对一组行（通常是分组后的数据）进行计算，并返回一个单一的汇总值。

4. 类型转换与格式化 (Casting and Formatting)

数值类型经常需要与其他类型（如字符串或日期）进行转换。

A. 显式类型转换 (Casting)

使用 :: 运算符或 CAST() 函数进行类型转换。

xxxxxxxxxx
8
1
-- 将字符串转换为整数
2
SELECT '123'::INTEGER;   -- 结果: 123
3
​
4
-- 将整数转换为双精度浮点数
5
SELECT 10::DOUBLE PRECISION; 
6
​
7
-- 将浮点数截断为整数 (注意是截断，不是四舍五入)
8
SELECT TRUNC(123.99);    -- 结果: 123

B. 格式化输出

使用 TO_CHAR() 函数将数值转换为特定格式的字符串，常用于报表生成。

xxxxxxxxxx
2
1
-- 将数值格式化为货币形式 (例如: $1,234.56)
2
SELECT TO_CHAR(1234.56, 'FM9,999.00'); -- 结果: 1,234.56

5. 常用数学函数 (Common Math Functions)

PostgreSQL 内置了许多标准的数学函数：