postgresql-快速入门

发表于 分类于 阅读次数： 本文字数： 13k 阅读时长 ≈ 12 分钟 
1
2
3
4
5
6
作者: 夜泊1990
企鹅: 1611756908
Q 群: 948233848
邮箱: hd1611756908@163.com
博客: https://hs-an-yue.github.io/
B 站: https://space.bilibili.com/514155929/

第一章 概述

第1节 介绍

PostgreSQL 是一个开源的关系型数据库，和 MySQL、Oracle 属于同一类数据库

第2节 起源

它最初源于加州大学伯克利分校的 POSTGRES 项目，目前由全球社区持续维护。

第3节 和MySQL区别

对比 PostgreSQL MySQL
类型 关系型数据库 关系型数据库
开源 ✅ ✅
事务 ✅ ✅
SQL 支持 ⭐⭐⭐⭐⭐ ⭐⭐⭐⭐
JSON 非常强 很强
地理空间 PostGIS 非常强 有 GIS
学习难度 稍高 相对简单
国内使用普及度 较高 很高

第二章 安装

第1节 官网

1
https://www.postgresql.org/

第2节 安装

Windows 系统命令

1
docker run -d --name pg18-gis -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=gis_main -e TZ=Asia/Shanghai -v pg18_postgis_data:/var/lib/postgresql --restart=always postgis/postgis:18-3.6

Linux系统命令

1
2
3
4
5
6
7
8
9
10
11
docker run -d \
  --name pg18-gis \
  -p 5432:5432 \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=gis_main \
  -e TZ=Asia/Shanghai \
  -v /opt/pg18_data:/var/lib/postgresql \
  --restart=always \
  postgis/postgis:18-3.6

第3节 客户端

自带客户端(psql) - 通过容器进入

1
docker exec -it 容器名称/容器ID psql -U postgres -d gis_main
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
psql基础命令

# 查看帮助
\?        # psql元命令帮助
\h        # SQL语法帮助，例 \h CREATE DATABASE

# 库相关
\l        # 列出所有数据库（就是你刚才看到的postgres template0 template1 template_postgis）
\l+       # 列出数据库+大小、详细信息
\c test_db   # 切换连接到 test_db 数据库（\connect简写）
\conninfo     # 查看当前连接信息

# Schema、表、扩展（GIS高频）
\dn          # 列出当前库所有schema
\dn+         # schema+权限
\dt          # 列出当前库的表
\dt+         # 表+大小注释
\d test_table1 # 查看表结构（字段、类型、索引、约束）
\d+ test_table1
\dx          # 查看当前库安装的扩展（用来看PostGIS！）
\du          # 列出角色/用户

# 其他实用
\r           # 清空当前输入缓冲区
\q           # 退出psql（quit）

官方提供GUI - pgAdmin4

1
略

第三方GUI - DBeaver

1
自己下载安装 (略)

第三章 库介绍(MySQL vs PG)

MySQL

1
2
3
4
5
6
7
MySQL实例（服务）
├─ Database（数据库/库）
│  ├─ Table 表
│  ├─ View 视图
│  └─ 函数、存储过程

MySQL表直接放在库下面

PG

1
2
3
4
5
6
7
8
9
10
11
12
13
14
PG实例（服务）
├─ Database（数据库）
│  ├─ Schema（模式，很多人翻译成“方案”）
│  │  ├─ public（默认自带schema！最重要）
│  │  │   └─ Table、视图、函数、空间表
│  │  ├─ tenant_01（自定义schema，做多租户）
│  │  └─ tenant_02
│  └─ 其他schema

PG和MySQL不同，PG的表不是直接在数据库中，PG在库和表之间多了一层Schema

在Schema下，默认带一个public的Schema，还可以自定义多个Schema，PG的表放在Schema下进行管理

在MySQL的用户看来，PG中的Schema更像数据库。

第四章 SQL语句

第1节 数据库管理

PG安装好后会自带系统默认库 postgres，不要把自己的业务数据存到这个库中，针对自己的业务要新建自己的业务库。

1
2
3
4
5
6
7
PG默认库

1. postgres(系统自带默认库)		
2. template0(系统自带默认库)
3. template1(系统自带默认库)

虽然三个库都有各自的用途，但是针对使用者来说，关系不大，这里不做介绍，下面用到再说。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- 创建数据库

最简写法:
CREATE DATABASE test_db;

描述: 
CREATE DATABASE 创建库的关键字，test_db自定义的库名，命名见名知意即可。
不需要像MySQL那样指定编码，PG默认utf8，不像MySQL那样utf8还分为utf8mb3和utf8mb4


指定模板:
CREATE DATABASE test_db TEMPLATE template0;

描述:
template0:PG默认自带的库(不可修改)
template1:PG默认自带的库(可以修改,但是很少有人修改)
PG在创建数据库的时候必须基于template0或者template1进行创建，不指定默认是template1，上面的最简写法就是。
PG的想法很简单，你要创建一个库，不知道怎么配置这个库，官方给你两个库，你找一个根据它进行复制就行了。


完整写法:
CREATE DATABASE test_db TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'zh_CN.UTF-8' LC_CTYPE 'zh_CN.UTF-8' CONNECTION LIMIT -1; -- -1代表不限制连接数（注意：LC_COLLATE 指定的 locale 需容器内已生成，否则会报 invalid locale name）

-- 删除数据库

最简写法:
DROP DATABASE test_db;

安全写法:
DROP DATABASE IF EXISTS test_db;



注意:⚠️

有会话连接到目标库时，无法删除，需要强制断开连接，再删除。

第一步: 强制断开连接
-- 终止所有连接到 test_db 的会话
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'test_db' AND pid <> pg_backend_pid();
第二步: 删除
-- 删除数据库
DROP DATABASE IF EXISTS test_db;

第2节 数据库表管理

2.1 数据结构(常用)

分类 MySQL PostgreSQL 差异说明 & 业务建议
整型 tinyint / smallint / int / bigint smallint / int / bigint 取值范围基本一致 & 主键推荐两者都用bigint
高精度小数 decimal(p,s) numeric(p,s) 等价，无精度丢失；GPS 坐标、金额优先选用
浮点数 float / double real / double precision 两者都存在精度丢失
字符串 varchar(n) / char(n) / longtext text（推荐，不限长） / varchar(n) / char(n) PG 中 text 与 varchar 性能相同，推荐 text；MySQL 大文本用 longtext 等，性能偏弱
时间类型 datetime（无时区） / timestamp（有 2038 上限） / date / time timestamp（无时区） / timestamptz（带时区） / date / time PG 优先 timestamptz；MySQL 业务时间推荐 datetime
布尔 tinyint(1)（伪布尔，约定 0/1） boolean（原生，true/false/null） MySQL 布尔底层是数字，容易产生判断异常；PG 是原生布尔
JSON json json（文本）jsonb（二进制，支持 GIN 索引） PG jsonb 能力强，支持包含、键值索引；MySQL JSON 索引能力弱
数组 无原生数组 原生支持 bigint[]、text[] MySQL 只能逗号分隔字符串（不可索引）场景：点位关联多张图片 ID，PG 数组更便捷

其他空间地理信息的数据类型，请查看官网。

2.2 创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- 部门表(主键由程序生成，如雪花 ID，不使用数据库自增)
CREATE TABLE sys_dept (
    dept_id        BIGINT           PRIMARY KEY,
    dept_name      TEXT             NOT NULL,
    parent_id      BIGINT           NOT NULL DEFAULT 0,
    sort           INT              DEFAULT 1,
    status         INT              NOT NULL DEFAULT 1,
    is_deleted     INT              DEFAULT 0,
    create_id      BIGINT,
    update_id      BIGINT,
    create_time    TIMESTAMPTZ      DEFAULT CURRENT_TIMESTAMP,
    update_time    TIMESTAMPTZ      DEFAULT CURRENT_TIMESTAMP
);

-- 表注释
COMMENT ON TABLE sys_dept IS '部门表';

-- 字段注释
COMMENT ON COLUMN sys_dept.dept_id IS '部门ID,主键';
COMMENT ON COLUMN sys_dept.dept_name IS '部门名称';
COMMENT ON COLUMN sys_dept.parent_id IS '父ID,最顶层从0开始';
COMMENT ON COLUMN sys_dept.sort IS '排序序号';
COMMENT ON COLUMN sys_dept.status IS '状态(1-正常 0-停用)';
COMMENT ON COLUMN sys_dept.is_deleted IS '逻辑删除标识(1-已删除 0-未删除)';
COMMENT ON COLUMN sys_dept.create_id IS '创建人ID,关联用户表(sys_user)主键';
COMMENT ON COLUMN sys_dept.update_id IS '更新人ID,关联用户表(sys_user)主键';
COMMENT ON COLUMN sys_dept.create_time IS '创建时间';
COMMENT ON COLUMN sys_dept.update_time IS '更新时间';

-- 索引（统一前缀命名：PG 中索引与表共享同一 schema 的命名空间，同一 schema 内索引名不能重复）
CREATE INDEX idx_sys_dept_dept_name ON sys_dept(dept_name);
CREATE INDEX idx_sys_dept_parent_id ON sys_dept(parent_id);
CREATE INDEX idx_sys_dept_sort ON sys_dept(sort);
CREATE INDEX idx_sys_dept_create_time ON sys_dept(create_time);
CREATE INDEX idx_sys_dept_update_time ON sys_dept(update_time);

-- 外键（前提：sys_user 表需已创建，含 user_id 主键、dept_id 字段，否则以下语句会报错）
-- create_id 创建人，关联 sys_user.user_id
ALTER TABLE sys_dept
    ADD CONSTRAINT fk_sys_dept_create_id
        FOREIGN KEY (create_id) REFERENCES sys_user(user_id);

-- update_id 更新人，关联 sys_user.user_id
ALTER TABLE sys_dept
    ADD CONSTRAINT fk_sys_dept_update_id
        FOREIGN KEY (update_id) REFERENCES sys_user(user_id);

ALTER TABLE sys_user
    ADD CONSTRAINT fk_sys_user_dept_id
        FOREIGN KEY (dept_id) REFERENCES sys_dept(dept_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- 部门表(主键自增 - GENERATED ALWAYS 不允许手动插入主键值；如需手动指定主键，改用 GENERATED BY DEFAULT)
CREATE TABLE sys_dept (
    dept_id        BIGINT           PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    dept_name      TEXT             NOT NULL,
    parent_id      BIGINT           NOT NULL DEFAULT 0,
    sort           INT              DEFAULT 1,
    status         INT              NOT NULL DEFAULT 1,
    is_deleted     INT              DEFAULT 0,
    create_id      BIGINT,
    update_id      BIGINT,
    create_time    TIMESTAMPTZ      DEFAULT CURRENT_TIMESTAMP,
    update_time    TIMESTAMPTZ      DEFAULT CURRENT_TIMESTAMP
);

-- 表注释
COMMENT ON TABLE sys_dept IS '部门表';

-- 字段注释
COMMENT ON COLUMN sys_dept.dept_id IS '部门ID,主键';
COMMENT ON COLUMN sys_dept.dept_name IS '部门名称';
COMMENT ON COLUMN sys_dept.parent_id IS '父ID,最顶层从0开始';
COMMENT ON COLUMN sys_dept.sort IS '排序序号';
COMMENT ON COLUMN sys_dept.status IS '状态(1-正常 0-停用)';
COMMENT ON COLUMN sys_dept.is_deleted IS '逻辑删除标识(1-已删除 0-未删除)';
COMMENT ON COLUMN sys_dept.create_id IS '创建人ID,关联用户表(sys_user)主键';
COMMENT ON COLUMN sys_dept.update_id IS '更新人ID,关联用户表(sys_user)主键';
COMMENT ON COLUMN sys_dept.create_time IS '创建时间';
COMMENT ON COLUMN sys_dept.update_time IS '更新时间';

-- 索引（统一前缀命名：PG 中索引与表共享同一 schema 的命名空间，同一 schema 内索引名不能重复）
CREATE INDEX idx_sys_dept_dept_name ON sys_dept(dept_name);
CREATE INDEX idx_sys_dept_parent_id ON sys_dept(parent_id);
CREATE INDEX idx_sys_dept_sort ON sys_dept(sort);
CREATE INDEX idx_sys_dept_create_time ON sys_dept(create_time);
CREATE INDEX idx_sys_dept_update_time ON sys_dept(update_time);

-- 外键（前提：sys_user 表需已创建，含 user_id 主键、dept_id 字段，否则以下语句会报错）
-- create_id 创建人，关联 sys_user.user_id
ALTER TABLE sys_dept
    ADD CONSTRAINT fk_sys_dept_create_id
        FOREIGN KEY (create_id) REFERENCES sys_user(user_id);

-- update_id 更新人，关联 sys_user.user_id
ALTER TABLE sys_dept
    ADD CONSTRAINT fk_sys_dept_update_id
        FOREIGN KEY (update_id) REFERENCES sys_user(user_id);

ALTER TABLE sys_user
    ADD CONSTRAINT fk_sys_user_dept_id
        FOREIGN KEY (dept_id) REFERENCES sys_dept(dept_id);

2.3 删除表

1
2
3
4
5
基础删除
DROP TABLE IF EXISTS sys_dept;

如果被其他对象依赖，级联删除（连带引用它的视图、其他表的外键约束一起删掉，谨慎使用）
DROP TABLE IF EXISTS sys_dept CASCADE;

2.4 修改表

2.4.1 新增列
1
2
3
4
5
-- 新增 remark 字段
ALTER TABLE sys_dept ADD COLUMN IF NOT EXISTS remark TEXT;

-- 添加字段注释
COMMENT ON COLUMN sys_dept.remark IS '备注';
2.4.2 删除列
1
ALTER TABLE sys_dept DROP COLUMN IF EXISTS remark;
2.4.3 修改列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. 修改字段类型
ALTER TABLE sys_dept ALTER COLUMN 字段名 TYPE 新类型;

-- 2. 修改 NOT NULL 约束
ALTER TABLE sys_dept ALTER COLUMN 字段名 SET NOT NULL;
ALTER TABLE sys_dept ALTER COLUMN 字段名 DROP NOT NULL;

-- 3. 修改默认值
ALTER TABLE sys_dept ALTER COLUMN 字段名 SET DEFAULT xxx;
ALTER TABLE sys_dept ALTER COLUMN 字段名 DROP DEFAULT;

-- 4. 修改字段注释（独立语句）
COMMENT ON COLUMN sys_dept.字段名 IS '新注释';

-- 5. 重命名字段（不改类型）
ALTER TABLE sys_dept RENAME COLUMN old_name TO new_name;
2.4.4 查询表结构
1
2
-- 不同 schema 可能有同名表，建议加上 table_schema 限定
SELECT * FROM information_schema.columns WHERE table_name='sys_dept' AND table_schema='public';

第3节 用户权限管理

1
2
3
4
-- 修改密码
ALTER ROLE yaya PASSWORD 'NewPass@2026';

其他权限控制，请自行查资料

第五章 导入和导出

前面几章把库和表都建好了,这一章解决数据怎么进、怎么出的问题。日常就两个场景:

  • 整库备份/还原 —— 用 pg_dump / pg_restore
  • 单表数据导入导出 —— 用 COPY / \copy(CSV 最常用)

物理备份、主从复制属于运维范畴,本文不展开。

第1节 工具概览(MySQL vs PG)

用途 MySQL PostgreSQL
备份单个库 mysqldump 库名 pg_dump -d 库名
还原 mysql 库名 < backup.sql psql -d 库名 -f backup.sql
备份整个实例 mysqldump –all-databases pg_dumpall
压缩格式备份 管道配合 gzip 压缩 pg_dump -Fc 自带压缩,配套 pg_restore 还原
表数据导出 SELECT … INTO OUTFILE \copy TO
表数据导入 LOAD DATA INFILE \copy FROM

我们的 PG 跑在 Docker 里,所以下面统一用 docker exec 方式演示(方便,不用进容器交互),容器名用第二章的 pg18-gis。

如果宿主机自己装了 PG 客户端,给命令加上 -h 127.0.0.1 -p 5432 也能直接连(密码是创建容器时设的 postgres)。还是推荐容器内执行:pg_dump 版本和数据库版本天然匹配,不会踩版本兼容的坑。

第2节 整库备份和还原(pg_dump/pg_restore)

2.1 备份

备份成 SQL 纯文本(默认格式,可读可改,跨版本通用)

1
2
3
4
5
# 在容器里生成备份文件
docker exec pg18-gis pg_dump -U postgres -d gis_main -f /tmp/gis_main.sql

# 从容器拷回宿主机
docker cp pg18-gis:/tmp/gis_main.sql ./gis_main.sql

备份成自定义格式(-Fc:自带压缩,文件更小,还原更灵活,推荐做正式备份)

1
2
docker exec pg18-gis pg_dump -U postgres -d gis_main -Fc -f /tmp/gis_main.dump
docker cp pg18-gis:/tmp/gis_main.dump ./gis_main.dump

⚠️ Windows 下不要写成 docker exec ... > dump.sql 这种重定向导出:PowerShell 会按 UTF-16 编码写文件,导致备份文件损坏。统一用 -f 生成到容器里、再 docker cp 拷出来最稳。

只备份结构 / 只备份数据

1
2
3
4
5
# 只导结构(建表、索引、注释等,不含数据)
docker exec pg18-gis pg_dump -U postgres -d gis_main -s -f /tmp/only_ddl.sql

# 只导数据(不含建表语句)
docker exec pg18-gis pg_dump -U postgres -d gis_main -a -f /tmp/only_data.sql

只备份某张表 / 某个 schema

1
2
3
4
5
# 只备份 sys_dept 表
docker exec pg18-gis pg_dump -U postgres -d gis_main -t sys_dept -f /tmp/sys_dept.sql

# 只备份 public 这个 schema
docker exec pg18-gis pg_dump -U postgres -d gis_main -n public -f /tmp/public.sql

常用参数

参数 作用
-U 用户名 指定连接用户
-d 库名 指定要备份的数据库
-f 文件 输出到文件
-t 表名 只备份指定表(可写多次,备份多张)
-n schema名 只备份指定 schema
-s 只导结构(不含数据)
-a 只导数据(不含结构)
-Fc 自定义格式(压缩,配套 pg_restore 还原)
-Fd 目录格式(超大库可用 pg_restore -j 并行还原)

2.2 还原

⚠️ 关键点:pg_dump 备份的是库里的内容,不含建库语句(类似 mysqldump 不带 --databases 参数),所以还原前必须先手工建好空库。

还原 SQL 文本格式

1
2
3
4
5
6
7
8
# 1. 先建好空库
docker exec pg18-gis psql -U postgres -c "CREATE DATABASE gis_main_new"

# 2. 把备份文件拷进容器
docker cp ./gis_main.sql pg18-gis:/tmp/gis_main.sql

# 3. 还原(-v ON_ERROR_STOP=1:遇到第一个错误就停,避免错误被日志淹没)
docker exec pg18-gis psql -U postgres -d gis_main_new -v ON_ERROR_STOP=1 -f /tmp/gis_main.sql

还原自定义格式(-Fc 备份的文件必须用 pg_restore 还原)

1
2
3
4
5
6
7
8
9
# 同样先建好空库,再拷文件进容器
docker exec pg18-gis psql -U postgres -c "CREATE DATABASE gis_main_new"
docker cp ./gis_main.dump pg18-gis:/tmp/gis_main.dump

# 还原
docker exec pg18-gis pg_restore -U postgres -d gis_main_new /tmp/gis_main.dump

# 如果目标库里已有同名对象,加 -c --if-exists 先删再建
docker exec pg18-gis pg_restore -U postgres -d gis_main_new -c --if-exists /tmp/gis_main.dump

小习惯:备份完顺手在测试库还原验证一次,避免关键时刻备份不可用。

2.3 备份整个实例(pg_dumpall)

场景:整个 PG(所有库 + 用户角色 + 权限)搬家

1
2
3
4
5
6
7
# 备份
docker exec pg18-gis pg_dumpall -U postgres -f /tmp/pg_all.sql
docker cp pg18-gis:/tmp/pg_all.sql ./pg_all.sql

# 还原(连到 postgres 库执行,脚本里自带建库、建角色语句)
docker cp ./pg_all.sql pg18-gis:/tmp/pg_all.sql
docker exec pg18-gis psql -U postgres -v ON_ERROR_STOP=1 -f /tmp/pg_all.sql

pg_dump vs pg_dumpall

对比 pg_dump pg_dumpall
范围 单个库 整个实例(所有库+角色+权限)
格式 SQL / 自定义 / 目录 只有 SQL 文本
还原方式 psql 或 pg_restore psql
典型场景 日常单库备份 整机迁移、大版本升级

第3节 表级导入导出(COPY / \copy)

单表数据进出最常用的就是 CSV。PG 原生的 COPY 命令,比逐条 INSERT 快几个数量级,对应 MySQL 的 LOAD DATA INFILE / SELECT ... INTO OUTFILE。

同样的功能有两个版本,一句话区分:

命令 谁执行 文件在哪 权限要求
COPY 数据库服务端 服务器(容器)上的路径 需要超级用户或 pg_read_server_files / pg_write_server_files
\copy psql 客户端 执行 psql 的机器上的路径 普通用户即可

✅ 日常推荐用 \copy:不用超级用户,更安全,和 MySQL 的使用习惯最接近。

⚠️ 因为本文的 psql 是 docker exec 进容器执行的,所以 \copy 的文件实际也落在容器里,来回都需要 docker cp(见下面示例)。

3.1 导出 CSV

1
2
3
4
5
6
7
# 进入 psql 后执行(进入方式见第二章)

# 最简写法:导出整表
\copy sys_dept TO '/tmp/sys_dept.csv' CSV HEADER

# 导出查询结果(可挑列、加条件)
\copy (SELECT dept_id, dept_name FROM sys_dept WHERE status = 1) TO '/tmp/sys_dept_ok.csv' CSV HEADER
1
2
# 再从容器把文件拷到宿主机
docker cp pg18-gis:/tmp/sys_dept.csv ./sys_dept.csv

3.2 导入 CSV

⚠️ 前置条件:目标表必须先建好,且 CSV 中列的顺序要和表列一一对应(带表头时,HEADER 只是让导入时跳过第一行,并不会按列名自动匹配)。

1
2
3
4
5
6
7
8
9
# 先把 CSV 拷进容器
docker cp ./sys_dept.csv pg18-gis:/tmp/sys_dept.csv

# 进入 psql 后执行
\copy sys_dept FROM '/tmp/sys_dept.csv' CSV HEADER

# 如果 CSV 列顺序与表不一致(比如 CSV 里是 名称在前、ID 在后),
# 可写列清单建立对应关系 —— 列清单按 CSV 的列顺序列出表列名
\copy sys_dept (dept_name, dept_id) FROM '/tmp/sys_dept.csv' CSV HEADER

常用选项

选项 作用
CSV 逗号分隔(不写默认是制表符分隔)
HEADER 第一行是列名,导入时自动跳过
ENCODING ‘UTF8’ 指定文件编码(中文乱码时检查这个)

完整写法(选项都写进 WITH,更直观):

1
2
\copy sys_dept TO '/tmp/sys_dept.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')
\copy sys_dept FROM '/tmp/sys_dept.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')

3.3 带 geometry(空间字段)的表

GIS 场景的坑:COPY/\copy 不认识 WKT,不能直接往 geometry 列里导。

导出 —— geometry 列默认会导出成十六进制 EWKB,想要 WKT 得显式转换:

1
\copy (SELECT id, name, ST_AsText(geom) FROM poi) TO '/tmp/poi.csv' CSV HEADER

导入 —— 不能直接进 geometry 列,分两步:先落到文本临时表,再转换入库:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. 建临时表,geom 先用 text 存
CREATE TABLE poi_tmp (
    id        BIGINT,
    name      TEXT,
    geom_text TEXT
);

-- 2. 导入临时表(\copy 在 psql 里执行)
\copy poi_tmp FROM '/tmp/poi.csv' CSV HEADER

-- 3. 转成 geometry 后写入正式表(poi 换成你的实际表;4326 是坐标系 SRID,按实际数据改)
INSERT INTO poi (id, name, geom)
SELECT id, name, ST_GeomFromText(geom_text, 4326) FROM poi_tmp;

-- 4. 删掉临时表
DROP TABLE poi_tmp;

第4节 图形化工具(简略)

pgAdmin4

右键数据库 → Backup… / Restore…;右键表 → Import/Export Data…(本质还是调用 pg_dump 和 COPY)

DBeaver

右键表 → 导出数据 / 导入数据(支持 CSV、Excel,本质也是生成 COPY 语句)

GUI 是外壳,底层的 pg_dump / pg_restore / COPY 才是本体,命令行会用了,GUI 就是点两下的事。

--------------------------已经到底啦！--------------------------