随笔分类 -  pgsql

pgsql语法
pgsql中的jdbc连接参数配置（优化数据批量插入）
摘要：url: jdbc:postgresql://192.168.20.10:5432/库名称?reWriteBatchedInserts=true reWriteBatchedInserts=true；控制是否将批量插入语句转换成更高效的形式 阅读全文
posted @ 2023-09-04 09:18 懂得归零 阅读(1258) 评论(0) 推荐(0)
pgsql数据库清除表磁盘冗余
摘要：VACUUM FULL xxx; 阅读全文
posted @ 2023-07-06 09:25 懂得归零 阅读(18) 评论(0) 推荐(0)
pgsql统计表数据大小
摘要：select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc; 阅读全文
posted @ 2023-06-30 15:11 懂得归零 阅读(124) 评论(0) 推荐(0)
pgsql死锁解决
摘要：select * from pg_stat_activity where datname = 'datname' and waiting_reason = 'lock'; 根据查询结果中的pid值杀掉进程 select pg_terminate_backend(pid) 阅读全文
posted @ 2023-05-24 09:20 懂得归零 阅读(61) 评论(0) 推荐(0)
pgsql中行数据转json数组
摘要：SELECT array_to_json(array_agg(row_to_json(sys_xzqh))) from sys_xzqh where xzqh like '%341126%' 阅读全文
posted @ 2023-02-10 09:15 懂得归零 阅读(186) 评论(0) 推荐(0)
pgsql中over函数的应用
摘要：-- sum() over(partition by ... order by ...)SELECT len/sum(len)over(partition by road_id) param from rs; -- min() over(partition by ... order by ...)S 阅读全文
posted @ 2022-12-19 10:25 懂得归零 阅读(785) 评论(0) 推荐(0)
pgsql计算占比
摘要：SELECT len/sum(len)over(partition by road_id) param from road_jcpd_section 阅读全文
posted @ 2022-12-16 10:16 懂得归零 阅读(220) 评论(0) 推荐(0)
having对聚合函数的结果集进行过滤
摘要：SELECT rl.road_code,string_agg(distinct rs.tech_level_label, ',') from road_lst rlleft join road_section rs on rl.id = rs.road_idwhere rs.tech_level_l 阅读全文
posted @ 2022-12-13 14:03 懂得归零 阅读(42) 评论(0) 推荐(0)
pgsql中物化视图的使用
摘要：1、创建物化视图 CREATE MATERIALIZED VIEW "view_xxx“ as select * from 表 2、刷新物化视图 refresh materialized view view_xxx; 3、删除物化视图 drop materialized view view_xxx; 阅读全文
posted @ 2022-12-13 13:32 懂得归零 阅读(765) 评论(0) 推荐(0)
递归分批次插入数据（An I/O error occurred while sending to the backend报错解决方案）
摘要：//递归插入public void add(List<Object> all, long start, long limit){ //截取 List<Object> collect = all.stream().skip(start).limit(limit).collect(Collectors. 阅读全文
posted @ 2022-12-05 11:20 懂得归零 阅读(1666) 评论(0) 推荐(0)
pgsql指定部分字段去重
摘要：-- 基于ig.start_pile,ig.end_pile 字段去重 with ete as ( SELECT * from (SELECT ROW_NUMBER() OVER(PARTITION by ig.start_pile,ig.end_pile ORDER BY ig.start_pil 阅读全文
posted @ 2022-10-28 11:25 懂得归零 阅读(1136) 评论(0) 推荐(0)
pgsql临时表
摘要：with ete as ( SELECT * from table_name ) SELECT * from ete 阅读全文
posted @ 2022-10-28 11:24 懂得归零 阅读(90) 评论(0) 推荐(0)
pgsql给表重命名
摘要：alter table "pavement_damage_dtl_temp" rename to "pavement_damage_dtl"; 搜索 复制 阅读全文
posted @ 2022-09-26 09:16 懂得归零 阅读(231) 评论(0) 推荐(0)
pgsql给表字段设置默认值及设置主键
摘要：ALTER TABLE pavement_damage_dtl_temp add PRIMARY key(id); ALTER table pavement_damage_dtl_temp alter is_manual_work set DEFAULT FALSE; 搜索 复制 阅读全文
posted @ 2022-09-26 09:15 懂得归零 阅读(839) 评论(0) 推荐(0)
pgsql表结构复制
摘要：CREATE TABLE pavement_damage_dtl_temp(like pavement_damage_dtl); 搜索 复制 阅读全文
posted @ 2022-09-26 09:14 懂得归零 阅读(80) 评论(0) 推荐(0)