PostgreSQL基础使用

常用数据类型一、整数类型

SMALLINT - 2字节，-32768 到 +32767
INT - 4字节，-2147483648 到 +2147483647
BIGINT - 8字节，-9223372036854775808 到 +9223372036854775807
SERIAL - 自动递增整数（4字节，约21亿亿条）
BIGSERIAL - 大范围自增整数（8字节，约922亿亿条）
二、精确小数
NUMERIC(10,2) 表示总共10位，2位小数
三、字符类型
CHAR(n) - 定长字符串，不足补空格
VARCHAR(n) - 变长字符串，最大长度n
TEXT - 不限长度文本
四、日期时间类型
DATE - 日期，如 '2024-01-15'
TIME - 时间，如 '14:30:00'
TIMESTAMP - 日期和时间（有时区）
五、布尔类型
BOOLEAN - 真/假值
六、二进制类型
BYTEA - 二进制数据（类似BLOB）
七、JSON/XML 类型
JSONB - 二进制 JSON（推荐，支持索引）
XML - XML 数据
八、数组类型
任何类型都可以加上 [] 变成数组
INT[] - 整数数组
TEXT[] - 文本数组
九、特殊类型
ENUM - 枚举类型（需先创建）CREATE TYPE content_type AS ENUM ('article');

数据操作

一、数据转换
值::类型  
SELECT CURRENT_TIMESTAMP::DATE;
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');

 索引

一、创建复合索引并且加上判断
CREATE UNIQUE INDEX idx_name 
ON newtable (column2,column3)
WHERE isdelete = 0;

锁

一、行级锁
select * from newtable n FOR UPDATE

二、查看当前所有锁
SELECT
locktype,
database AS db_oid,
relation AS rel_oid,
pid,
mode,
granted
FROM pg_locks;
关键字段说明：
locktype：锁类型（如relation表锁、tuple行锁）
pid：持有锁的进程ID
mode：锁模式（如AccessShareLock、RowExclusiveLock）
granted：是否已获取锁（false表示被阻塞）

三、阻塞进程定位与终止

SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks l1 ON l1.pid = blocked.pid
JOIN pg_locks l2 ON l2.locktype = l1.locktype
AND l2.DATABASE IS NOT DISTINCT FROM l1.DATABASE
AND l2.relation IS NOT DISTINCT FROM l1.relation
AND NOT l1.granted
AND l2.granted
JOIN pg_stat_activity blocking ON blocking.pid = l2.pid;
blocked_pid：被阻塞的进程ID
blocking_pid：阻塞源进程ID
blocked_query/blocking_query：对应SQL语句

四、终止单个进程
SELECT pg_terminate_backend(pid);

分析

一、查询执行分析工具
EXPLAIN ANALYZE
select '1'::int as d