年初搓项目的时候用到了 PostgreSQL，整理一篇即看即用的快速上手教程

全文共计7209字，预计阅读时间 12min

SpringBoot整合PostgreSQL

引入依赖

<!--数据库连接池-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>

https://mvnrepository.com/artifact/org.postgresql/postgresql

版本号这里查

配置

配置数据源信息

spring:
  profiles:
    active: dev
  application:
    main:
      allow-circular-references: true
  datasource:
    url: jdbc:postgresql://${auris.datasource.host:localhost}:${auris.datasource.port:5432}/${auris.datasource.database:auris}?sslmode=disable
    driver-class-name: ${auris.datasource.driver-class-name:org.postgresql.Driver}
    username: ${auris.datasource.username:postgres}
    password: ${auris.datasource.password:1234}

对比MySQL

数据类型差异

总结：

• 整数、浮点、字符串、DATE→TIME→TIMESTAMP 基本照抄；
• TINYINT(1)-> 布尔、BLOB→BYTEA、DATETIME→TIMESTAMP、ENUM 先建类型 必须改；
• JSONB、UUID、数组、范围、IP 类型 是 PG 独有

SQL差异与迁移

操作 MySQL 的 SQL 语句，换成 PostgreSQL 后 SQL语句怎么改？

90 % 的“日常”语句两边都能跑，但是有 10 % 的差别

一、不用变

1. 标准 DML
   SELECT / INSERT / UPDATE / DELETE / WHERE / ORDER BY / GROUP BY / HAVING / JOIN
2. 最常见的数据类型
   INT, BIGINT, VARCHAR(n), TEXT, DATE, TIME, DATETIME/TIMESTAMP, DECIMAL, FLOAT, DOUBLE, BOOLEAN
3. 主键、外键、唯一、检查、非空约束
4. 事务四大隔离级别（RC、RR、Serializable）
   注：PostgreSQL 的 RR 实现比 MySQL 更严格，但语法一样。

二、必须改，否则报错或行为异常

三、可不改，但性能/结果会变

四、实战迁移 checklist

1. 全局把 ` 换成 “（或用小写标识符直接弃用引号）。
2. 所有 AUTO_INCREMENT 换成 SERIAL 或 GENERATED ... IDENTITY。
3. 把 IFNULL/ISNULL 批量替换成 COALESCE。
4. 把 LIMIT m,n 改成 LIMIT n OFFSET m。
5. 把 ON DUPLICATE KEY UPDATE 改写成 ON CONFLICT ... DO UPDATE SET。
6. 用 pgloader 或 AWS DMS 做一次性数据迁移，会自动把 TINYINT(1) 映射成 BOOLEAN，把 ENUM 映射成 VARCHAR+CHECK。
7. 回归测试所有带 GROUP BY 的接口，确保列都在 GROUP BY 或聚合里。

——“增删改查 + 普通 JOIN” 基本无缝转换； 用到自增、分页、函数、Upsert、枚举、反引号，就必须改。

指令

以下操作基于已运行的 PostgreSQL Docker 容器（容器名 postgresql）

1.连接数据库

docker exec -it postgresql bash
psql -U postgres

2.数据库管理

# 查看所有数据库
\l

# 创建库
create database student; # 推荐小写+下划线命名 (未加引号的标识符自动转小写)

# 查看所有数据库
\l

# 删除库
drop database student;

# 切换数据库
\c database_name

3.元命令

使用navicat操作pg

一、连上 PostgreSQL

1. 打开 Navicat → 文件 → 新建连接 → PostgreSQL。
2. 填写四项即可：
   • 主机：localhost（或远程 IP）
   • 端口：5432
   • 初始数据库：postgres（默认维护库）
   • 用户名/密码：安装时设定的超级用户（如 postgres / ******）
3. 点“测试连接”→ 成功 → 确定。左侧出现蓝色大象图标，说明已连上。

二、创建新数据库

3 个空格“必须”填/选，其余全部留空（保持 Navicat 给的默认值）即可

字段	要不要填	怎么填/选
数据库名称	✅必填	填你自己的库名，例如 auris
所有者	✅必填	下拉选 postgres（超级用户）或你事先建好的业务账号
编码	✅必填	下拉选 UTF8（通用中英文）
模板	❌留空	默认 template1，Navicat 会自动带
排序规则 & 字符分类	❌留空	默认继承系统区域（zh_CN.UTF-8），后期可改
表空间	❌留空	默认 pg_default
连接限制	❌留空	保持 -1（无限制）
允许连接	❌留空	保持勾选
是模板	❌留空	不勾，普通业务库用不到

三、打开数据库并建 Schema（可选）

PostgreSQL 默认是 public schema，可直接用
如果想隔离业务，就新建一个模式，在里面建表：
右键数据库 → 新建模式 → 取名 sales → 确定。

四、建表

1. 展开 db_demo → 表 → 右键“新建表”。
2. 在下方表格里依次填列：
   text 复制

   id          serial          not null   ← 主键
   name        varchar(50)     not null
   birth_date  date            null
   salary      numeric(12,2)   default 0
   

3. 点“约束”页签 → 添加主键 → 选 id → 命名 pk_emp。
4. 保存 → 表名 employee → 确定。
   结果：Navicat 自动帮你生成 serial 序列、主键索引、COMMENT 都带好了。

五、插数据 & 查数据

1. 双击 employee → 界面右下角点“+”号即可可视化插行（类似 Excel）。
2. 或打开“查询”→ 新建查询：
   sql 复制

   INSERT INTO employee(name,birth_date,salary)
   VALUES ('张三','1990-06-15',8800);
   SELECT * FROM employee;
   

3. 点绿色三角运行 → 结果网格即时出现。

六、导出/逆向

1. 右键任意表 → 转储 SQL 文件 → 结构+数据 → 保存为 employee.sql
2. 数据库迁移：把 MySQL 连接也建在 Navicat 里 → 工具 → 数据传输 → 源选 MySQL、目标选 PostgreSQL → 勾“转换字段类型”→ 开始，Navicat 会自动把 AUTO_INCREMENT 转成 serial，DATETIME 转成 TIMESTAMP，省去手工改写

也可使用命令行工具 pg_dump 稳定导出数据库

# 导出整个数据库（结构 + 数据）
pg_dump -h localhost -U your_username -d your_database_name > backup.sql

# 只导出 public schema 的结构和数据
pg_dump -h localhost -U your_username -n public -d your_database_name > public_backup.sql

# 只导出结构（无数据）
pg_dump -h localhost -U your_username -s -d your_database_name > schema_only.sql