pg操作命令

相关文档

# pg官方文档
https://www.postgresql.org/docs/11/reference.html

# pg库安装步骤
https://www.cnblogs.com/rbcd/articles/18563583

PG通过SQL语句读取二进制bytea类型并进行二进制和十六进制转换

1、将二进制编码为十六进制
select encode("AUUID_0",'hex') from T;

2、将十六进制解码为二进制
select decode('8560377a6d2bf34ab1f2784adc78f500','hex')

pg使用jdbc连接时使用哪个schema

?currentSchema=chemaName

表复制

--pg复制表结构和数据（但不会复制原表的索引、主键、外键约束等）
CREATE TABLE new_table_name AS SELECT * FROM original_table_name;

--pg复制表结构不复制数据（但不会复制原表的索引、主键、外键约束等）
CREATE TABLE new_table_name AS SELECT * FROM original_table_name WHERE 1=0;

--pg仅复制表结构（但会复制原表的索引、主键、外键约束等）
CREATE TABLE new_table_name (LIKE original_table_name INCLUDING ALL);

--pg完全相同的表结构复制数据
INSERT INTO users_backup SELECT * FROM users;

--pg不太相同的表结构复制数据，需要指定字段
INSERT INTO users_backup (name, email) SELECT name, email FROM users;

--查询索引是否有效
select pg_class.relname, indisvalid AS is_valid
FROM pg_index
JOIN pg_class ON pg_index.indexrelid = pg_class.oid
WHERE pg_class.relname in(
	SELECT indexname FROM pg_indexes WHERE schemaname='public' AND tablename IN('tableName')
)

pg杀死执行的sql进程

--1. 查看当前被锁的进程和锁信息
SELECT 
    a.pid,
    usename,
    application_name,
    state,
    query,
    locktype,
    relation::regclass AS table_name,
    mode,
    granted
FROM 
    pg_locks l
JOIN 
    pg_stat_activity a ON l.pid = a.pid
WHERE 
    NOT granted; -- 只显示未被授予的锁（即等待中的锁）

--2. 查看哪些进程正在持有锁
SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start,
    now() - query_start AS duration
FROM 
    pg_stat_activity
WHERE 
    pid IN (
        SELECT pid FROM pg_locks WHERE NOT granted
    )
ORDER BY query_start;

--3. 强制终止阻塞的进程（解锁）
SELECT pg_terminate_backend(12345); -- 替换 12345 为实际的 pid

--4. 检查是否有长时间运行的事务
SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start,
    now() - query_start AS duration
FROM 
    pg_stat_activity
WHERE 
    state = 'active' AND 
    query_start < now() - interval '5 minutes';

--查询pg的当前活动执行sql进程
SELECT * FROM pg_stat_activity where state='active';

--结束进程
SELECT pg_cancel_backend(15006);

备份、恢复命令

# 执行备份恢复命令时，可以先设置密码变量，就可以不用输入密码
PGPASSWORD=真实库密码

# pg备份命令：
bin/pg_dump -h localhost -p 5432 -U postgres -d dbName -f /a.txt -n public
# 提示输入密码：-W
# 仅导出数据：--data-only
# 指定某张表：-t tableName

# pg恢复命令：
bin/psql -h localhost -p 5432 -U postgres -d dbName -f /a.txt

# 删除库名
PGPASSWORD=pwd dropdb -h localhost -p 5432 -U postgres deleteDbName

PostgreSQL数据库表唯一性约束失效问题

PostgreSQL数据库表唯一性约束失效，当存在空值时，会认为是非重复记录，可以使用函数把空值转成固定值-1
SELECT coalesce(null,-1)

sql查询

--pg树数据列表查询
WITH RECURSIVE cte(id,pid,dept_name) AS(
    -- 基础情况：选择顶级商品（一级目录）
    SELECT id, pid, dept_name FROM meta_dept_info WHERE pid IS NULL AND is_delete='0'
    UNION ALL
    -- 递归部分：选择每个商品的直接子商品
    SELECT t1.id, t1.pid, t1.dept_name FROM meta_dept_info t1
    JOIN cte t2 ON t1.pid = t2.id -- 关联到父商品
)
SELECT * FROM cte;