随笔分类 - pgsql

sql

摘要：一、postgreSQL认证文件 pg_hba.conf 配置文件pg_hba.conf的位置,通常情况下的路径是 /etc/postgresql/[VERSION]/main/pg_hba.conf 二、配置文件的内容 # TYPE DATABASE USER ADDRESS METHOD # " 阅读全文

posted @ 2021-10-27 17:03 hziwei 阅读(2130) 评论(0) 推荐(0) 编辑

pgsql 查询结果转换为json数组

摘要：select array_to_json(array_agg(row_to_json(t))) from (SELECT * FROM test) t 阅读全文

posted @ 2021-09-16 17:27 hziwei 阅读(2063) 评论(0) 推荐(0) 编辑

pgsql查询结果生成序列

摘要：一、row_number生成序列 select (row_number() over()) as id from generate_series(1,100) 二、根据指定列排序 select (row_number() over(order by test_id)) as id from test 阅读全文

posted @ 2021-09-16 16:46 hziwei 阅读(1541) 评论(0) 推荐(0) 编辑

pgsql 查询结果和查询行数

摘要：select count(*) over () as total, * from test 阅读全文

posted @ 2021-09-07 11:04 hziwei 阅读(1212) 评论(0) 推荐(0) 编辑

PostgreSQL 解析json字段

摘要：一、解析json数组 select json_array_elements(lv_num_json)->'l1' lv,json_array_elements(lv_num_json)->'num' num from ( select '[{"l1": 8, "num": 1}, {"l1": 9, 阅读全文

posted @ 2021-08-18 18:01 hziwei 阅读(2649) 评论(0) 推荐(0) 编辑