连接到数据库

psql -U 用户名 -d 数据库名 -h 主机地址 -p 端口号

使用 psql 客户端连接 PostgreSQL

查看当前连接信息

\conninfo

显示当前数据库连接详情

查看 PostgreSQL 版本

SELECT VERSION();

返回数据库服务器版本

退出 psql

\q

退出 psql 命令行工具

查看所有数据库

\l

psql 元命令，列出所有数据库

创建数据库

CREATE DATABASE 数据库名 OWNER 所有者;

创建一个新数据库，可指定所有者

切换数据库

\c 数据库名

切换到指定的数据库

删除数据库

DROP DATABASE 数据库名;

永久删除数据库，谨慎操作

查看当前数据库

SELECT CURRENT_DATABASE();

显示当前连接的数据库名

修改数据库

ALTER DATABASE 数据库名 RENAME TO 新名称;

重命名数据库

查看数据库大小

SELECT pg_database_size('数据库名');

返回数据库占用的磁盘空间

查看所有模式

\dn

psql 元命令，列出所有模式

创建模式

CREATE SCHEMA 模式名;

创建一个新模式

删除模式

DROP SCHEMA 模式名;

删除一个模式

级联删除模式

DROP SCHEMA 模式名 CASCADE;

强制删除模式及其下的所有对象

查看所有表

\dt

列出当前数据库中所有表

查看表结构

\d 表名

显示表的列、类型、约束等详细信息

查看表的详细信息

\dt+ 表名

显示包括注释、索引在内的更多信息

查看表的创建 SQL

SELECT pg_get_serial_sequence('表名','列名'); 或使用 pg_dump -t 表名

查看表的定义语句

创建表

CREATE TABLE 表名 (列名 数据类型 [约束], …);

删除表

DROP TABLE 表名;

条件删除表

DROP TABLE IF EXISTS 表名;

添加列

ALTER TABLE 表名 ADD COLUMN 列名 数据类型;

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

修改列数据类型

ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 新数据类型;

重命名列

ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;

重命名表

ALTER TABLE 旧表名 RENAME TO 新表名;

添加约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (条件);

删除约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

清空表数据

TRUNCATE TABLE 表名;

插入单行

INSERT INTO 表名 (列1, 列2, …) VALUES (值1, 值2, …);

插入多行

INSERT INTO 表名 (列1, 列2, …) VALUES (值1, 值2), (值3, 值4), …;

从查询结果插入

INSERT INTO 目标表 (列1, 列2) SELECT 列1, 列2 FROM 源表 WHERE 条件;

冲突时更新

INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2) ON CONFLICT (冲突列) DO UPDATE SET 列1 = 新值;

基本查询

SELECT 列1, 列2 FROM 表名 WHERE 条件;

查询所有列

SELECT * FROM 表名;

条件查询

SELECT * FROM 表名 WHERE 条件1 AND/OR 条件2;

排序查询

SELECT * FROM 表名 ORDER BY 列名 ASC/DESC;

分组查询

SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名;

分组后过滤

SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名 HAVING COUNT(*) > 值;

限制结果数量

SELECT * FROM 表名 LIMIT 数量 OFFSET 偏移量;

去重查询

SELECT DISTINCT 列名 FROM 表名;

更新数据

UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

删除数据

DELETE FROM 表名 WHERE 条件;

从多表删除

DELETE FROM 表1 USING 表2 WHERE 表1.列 = 表2.列 AND 条件;

返回修改的行

UPDATE 表名 SET 列 = 值 WHERE 条件 RETURNING *;

查看索引

\di 或 SELECT * FROM pg_indexes WHERE tablename = '表名';

创建普通索引

CREATE INDEX 索引名 ON 表名 (列名);

创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

创建复合索引

CREATE INDEX 索引名 ON 表名 (列1, 列2, …);

创建表达式索引

CREATE INDEX 索引名 ON 表名 (LOWER(列名));

创建部分索引

CREATE INDEX 索引名 ON 表名 (列名) WHERE 条件;

删除索引

DROP INDEX 索引名;

重建索引

REINDEX INDEX 索引名;

主键

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

外键

ALTER TABLE 表名 ADD FOREIGN KEY (列名) REFERENCES 父表(列名);

唯一约束

ALTER TABLE 表名 ADD UNIQUE (列名);

检查约束

ALTER TABLE 表名 ADD CHECK (列名 > 0);

非空约束

ALTER TABLE 表名 ALTER COLUMN 列名 SET NOT NULL;

开始事务

BEGIN; 或 START TRANSACTION;

开启一个新事务

提交事务

COMMIT;

提交当前事务，永久保存修改

回滚事务

ROLLBACK;

回滚当前事务，撤销所有修改

设置保存点

SAVEPOINT 保存点名;

在事务中创建回滚点

回滚到保存点

ROLLBACK TO SAVEPOINT 保存点名;

回滚到指定保存点

释放保存点

RELEASE SAVEPOINT 保存点名;

删除保存点

查看当前事务隔离级别

SHOW TRANSACTION_ISOLATION;

返回当前会话的事务隔离级别

设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

可选值：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

锁定表

LOCK TABLE 表名 IN ACCESS EXCLUSIVE MODE;

对表加锁，用于显式控制并发

查看所有用户/角色

\du

psql 元命令，列出所有角色及权限

创建用户（可登录）

CREATE USER 用户名 WITH LOGIN PASSWORD '密码';

CREATE USER 是 CREATE ROLE … WITH LOGIN 的简写

创建角色（不可登录）

CREATE ROLE 角色名;

创建普通角色，通常用作权限组

创建超级用户

CREATE ROLE super_admin WITH SUPERUSER LOGIN PASSWORD '密码';

创建具有超级权限的用户

修改密码

ALTER ROLE 用户名 WITH PASSWORD '新密码';

修改用户密码

允许/禁止登录

ALTER ROLE 用户名 WITH LOGIN/NOLOGIN;

控制用户是否可登录

授予超级用户权限

ALTER ROLE 用户名 WITH SUPERUSER;

提升用户为超级用户

允许创建数据库

ALTER ROLE 用户名 WITH CREATEDB;

授予创建数据库权限

删除用户

DROP ROLE 角色名;

删除角色，需先确保无依赖对象

强制删除用户

DROP ROLE IF EXISTS 角色名 CASCADE;

级联删除所有关联权限

授予表权限

GRANT SELECT, INSERT, UPDATE, DELETE ON 表名 TO 用户名;

授予用户对表的特定操作权限

授予所有权限

GRANT ALL ON 表名 TO 用户名;

授予用户对表的所有权限

授予数据库权限

GRANT CONNECT, CREATE ON DATABASE 数据库名 TO 用户名;

授予用户连接和创建模式的权限

授予模式权限

GRANT USAGE ON SCHEMA 模式名 TO 用户名;

授予用户使用模式的权限

授予函数权限

GRANT EXECUTE ON FUNCTION 函数名 TO 用户名;

授予用户执行函数的权限

撤销权限

REVOKE 权限 ON 对象 FROM 用户名;

撤销用户权限

授予 PUBLIC

GRANT 权限 ON 对象 TO PUBLIC;

将权限授予所有用户

授予授予选项

GRANT 权限 ON 对象 TO 用户名 WITH GRANT OPTION;

允许用户将权限授予他人

查看用户权限

\du 或 SELECT * FROM information_schema.role_table_grants;

查看用户拥有的权限

\l 或 \list

列出所有数据库

\c 数据库名

切换到指定数据库

\dt

列出当前数据库中的所有表

\dt+

列出所有表（含注释、大小等信息）

\d 表名

查看指定表的结构

\d+ 表名

查看表结构（含存储参数、注释）

\di

列出所有索引

\dn

列出所有模式

\df

列出所有函数

\dv

列出所有视图

\du 或 \dg

列出所有角色和用户

\conninfo

显示当前连接信息

\x

切换扩展显示模式（垂直显示，适合宽表）

\pset format unaligned

设置无对齐输出格式（适合导出数据）

\pset border 0\|1\|2

设置表格边框样式

\H

切换 HTML 输出格式

\timing

切换 SQL 执行时间显示

\watch [秒数]

重复执行当前查询，用于监控

\i 文件名

执行指定 SQL 文件中的命令

\o 文件名

将查询结果重定向到文件

\copy

导入/导出数据（客户端侧）

\e

打开编辑器编辑当前查询缓冲区

\! 命令

在 shell 中执行外部命令

\?

查看所有元命令的帮助

\h SQL命令

查看指定 SQL 命令的语法帮助

SMALLINT

int2

2 字节整数，范围 -32768 到 32767

INTEGER

int, int4

4 字节整数，范围 -2³¹ 到 2³¹-1

BIGINT

int8

8 字节整数，范围 -2⁶³ 到 2⁶³-1

DECIMAL(p,s)

numeric

精确数字，可指定精度和小数位数

REAL

float4

单精度浮点数，4 字节

DOUBLE PRECISION

float8

双精度浮点数，8 字节

SERIAL

serial4

自动增长的 4 字节整数

BIGSERIAL

serial8

自动增长的 8 字节整数

CHAR(n)

character(n)

定长字符串，不足补空格

VARCHAR(n)

character varying(n)

变长字符串，最大 n 字符

TEXT

无

变长字符串，无长度限制

BYTEA

无

二进制数据

DATE

日历日期（年、月、日）

TIME

一天中的时间（无时区）

TIMESTAMP

日期和时间（无时区）

TIMESTAMPTZ

日期和时间（含时区）

INTERVAL

时间段

BOOLEAN

逻辑布尔值（真/假）

JSON

文本 JSON 数据

JSONB

二进制 JSON 数据，支持索引

UUID

通用唯一标识码

INET

IPv4 或 IPv6 主机地址

CIDR

IPv4 或 IPv6 网络地址

MACADDR

MAC 地址

备份为 SQL 脚本

pg_dump -U 用户名 -d 数据库名 > backup.sql

导出为纯文本 SQL 脚本

备份为自定义格式

pg_dump -U 用户名 -d 数据库名 -Fc -f backup.dump

压缩的自定义格式，支持选择性恢复

仅备份结构

pg_dump -U 用户名 -d 数据库名 --schema-only

只导出表结构，不导出数据

仅备份数据

pg_dump -U 用户名 -d 数据库名 --data-only

只导出数据，不导出结构

备份指定表

pg_dump -U 用户名 -d 数据库名 -t 表名 > table_backup.sql

只备份特定表

包含清理命令

pg_dump -U 用户名 -d 数据库名 --clean

在创建前添加 DROP 命令

备份远程数据库

pg_dump -h 主机 -p 端口 -U 用户名 -d 数据库名 > backup.sql

从远程数据库导出

备份所有数据库

pg_dumpall -U 用户名 > all_backup.sql

备份整个 PostgreSQL 集群的所有数据库

仅备份全局对象

pg_dumpall -U 用户名 --globals-only > globals.sql

只备份角色和表空间等全局对象

从 SQL 脚本恢复

psql -U 用户名 -d 数据库名 < backup.sql

执行 SQL 脚本恢复数据库

从自定义格式恢复

pg_restore -U 用户名 -d 数据库名 backup.dump

使用 pg_restore 恢复归档格式

选择性恢复

pg_restore -U 用户名 -d 数据库名 -t 表名 backup.dump

只恢复指定表

查看归档内容

pg_restore -l backup.dump

列出归档文件中的内容

查看执行计划

EXPLAIN SELECT …;

显示 SQL 语句的执行计划

实际执行并分析

EXPLAIN ANALYZE SELECT …;

实际执行并返回详细的执行统计信息

带缓冲区的分析

EXPLAIN (ANALYZE, BUFFERS) SELECT …;

显示缓冲区使用情况

详细输出

EXPLAIN (VERBOSE, ANALYZE) SELECT …;

输出更详细的执行信息

查看表统计信息

SELECT * FROM pg_stats WHERE tablename = '表名';

查看表的数据分布统计

分析表（更新统计）

ANALYZE 表名;

手动更新表的统计信息

查看表大小

SELECT pg_relation_size('表名');

返回表占用的磁盘空间

查看表总大小

SELECT pg_total_relation_size('表名');

返回表及索引的总大小

查看所有表大小

SELECT relname, pg_size_pretty(pg_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_relation_size(relid) DESC;

按大小排序显示所有用户表

查看当前活动连接

SELECT * FROM pg_stat_activity;

查看所有当前连接的会话信息

终止连接

SELECT pg_terminate_backend(pid);

终止指定 PID 的会话

查看锁信息

SELECT * FROM pg_locks;

查看当前持有的锁

查看当前配置

SHOW ALL;

显示所有配置参数

查看特定参数

SHOW shared_buffers;

查看指定参数的值

设置参数

SET work_mem = '16MB';

临时设置当前会话的参数

字符串

LENGTH(), UPPER(), LOWER(), CONCAT(), SUBSTRING()

字符串处理

日期时间

NOW(), CURRENT_DATE, EXTRACT(), DATE_TRUNC()

日期时间处理

数学

ROUND(), FLOOR(), CEIL(), ABS()

数学计算

聚合

COUNT(), SUM(), AVG(), MAX(), MIN()

聚合统计

条件

CASE WHEN … END, COALESCE(), NULLIF()

条件判断

JSON

jsonb_extract_path_text(), jsonb_array_elements()

JSON 数据操作

窗口函数

ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()

高级分析查询