pgsql使用积累系列_常用sql

 

写在前面：这不是一个知识系列博客，这只是对本人使用过知识的一个记录，最好的资源应该是官方文档。

官网doc: https://www.postgresql.org/docs/current/static/plpgsql.html

-- 时间处理
select now() + interval '1 month';
-- 字符串转时间
select DATE '2008-03-01'
-- 时间转字符串
select to_char(now(),'yy-MM-dd');

-- 新增/更新表后调整查询计划
autovacuum: ANALYZE public.crm_member_coupon

-- 创建sequence
create sequence users_id_seq increment by 1 minvalue 1 no maxvalue start with 1;
--使用sequence
nextval('users_id_seq')
-- 在表中使用sequence
-- 重置sequence
alter SEQUENCE users_id_seq restart with 1;


-- 活动sql查询
select * from pg_stat_activity 
where state = 'active';


-- 查看对象名称（表名）
SELECT c.tableoid::regclass, c.name, c.altitude
FROM cities c

-- 查看锁
select 
a.datid,
b.pid
b.locktype,
b.transactionid, 
from pg_stat_activity a
join pg_locks b on a.backend_xid = b.transactionid and a.wait_event=b.locktype
where state = 'active';

-- pgsql查询死锁的
select stat.pid,stat.wait_event,stat.wait_event_type,stat.query,
        clazz.oid,clazz.relname,
        lock.mode,lock.locktype,lock.relation
from pg_stat_activity stat,pg_class clazz,pg_locks lock
where lock.relation = clazz.oid and stat.pid = lock.pid 
and clazz.relname = '可疑的表';

-- 根据lock.mode对照下图找问题

-- 取消正在执行的sql（当前用户）
select pg_cancel_backend(pid);
-- 强杀
select pg_terminate_backend(pid);

-- copy 用法
-- encoding 'GBK' 解决中文乱码问题
-- 表中数据导出到文件
copy test_copy to 'D:\test_copy1.csv' delimiter ',' csv header encoding 'GBK';
-- 向表中导入数据
copy test_copy_2 from 'D:\test_copy1.csv' delimiter ',' csv header encoding 'GBK';

-- having子句用法
select ptype,name,count(0) from permitions group by ptype,name having ptype > 'read';
-- 批量统计
select ptype,name,count(0) from permitions group by grouping sets((ptype),(name),());
-- VALUES 用法
VALUES (1, 'one'), (2, 'two'), (3, 'three')
select * from (VALUES (1, 'one'), (2, 'two'), (3, 'three')) as t(num,letter);
-- WITH用法
explain with tmp as(
select id from users where firstname='zhang'
) 
select * from permitions where userid in (select * from tmp);

-- 递归用法（还需要再研究下）
WITH RECURSIVE t(n) AS (
    VALUES (1) -- 初始值 <==> n==1 
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100 --条件  <==> n<100;n++
)
SELECT sum(n) FROM t; -- 操作
-- WITH 实现DML事务
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)