PostgreSQL小总结

一些常用的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 2021-10-17 10:56:35
-- 查询版本号
select version();

-- 2021-10-18 18:21:08
-- 显示最大连接数
show max_connections

-- 查看事务状态
SHOW default_transaction_isolation;
SHOW transaction_isolation;

-- 2021-11-04 11:54:33
-- 测试 PostgreSQL 列计算
select 1/4 AS value;-- 0
select 11/4 AS value;-- 2
select ROUND(1::numeric/4::numeric,2) AS value;-- 0.25 => 1/4 保留两位小数
-- 例如(金额 money:元 -> 万 保留一位小数):SELECT ROUND(money::numeric/10000::numeric,1) feature_value FROM table ...

查询默认值返回

1
2
3
-- 2021-12-27 17:19:04
-- 默认值(查询 end_date = null 的时候,使用默认值 '2021-12-31')
SELECT id,start_date,COALESCE(end_date,'2021-12-31') FROM t_store_log WHERE id=323

序列

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 2021-10-21 10:09:43
-- 查看下一个序列号
-- '%s_id_seq' => %s 表名
SELECT nextval('t_cloak_user_id_seq');
SELECT nextval('t_task_center_log_id_seq');
--nextval(regclass)
--currval(regclass)
--lastval()
--setval(regclass, bigint)
--setval(regclass, bigint, boolean)

-- 一个库所有表恢复最大
SELECT CONCAT(E'SELECT setval(\'', relname, E'\', MAX(id)) FROM ', split_part(relname, '_id_seq', 1), ';') sql FROM pg_class WHERE relkind = 'S'

值替换

1
2
3
4
5
6
7
8
9
10
-- 2021-12-16 16:45:46
-- 值替换
-- [1] string 类型(把表 table_01 中varchar类型字段 rule_json_data 的值 aaa 全部替换为 bbb)
UPDATE table_01 SET rule_json_data=replace(rule_json_data,'aaa','bbb')
-- [2] json 类型(把表 table_01 中json类型字段 rule_json_data 的值 aaa 全部替换为 bbb。先把字段::text转为字符串,替换后的结果::json转为json类型赋值给rule_json_data字段)
UPDATE table_01SET rule_json_data=replace(rule_json_data::text,'aaa','bbb')::json
-- [3] 转义符。在要替换的字符串前面加大写的 E,在内容中'单引号前面加 \ 反斜杠
-- 把 scr.region_type='STORE' 修改为 scr.region_type='BRANCH'
UPDATE t_sc_feature_template_rule
SET rule_json_data=replace(rule_json_data::text,E'scr.region_type=\'STORE\'',E'scr.region_type=\'BRANCH\'')::json;

json解析/行转列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- PG case when 行转列
-- PG JSON 类型解析
-- ->> 双箭头获取字符串值 sales
-- -> 单箭头获取字符串 "sales"
select
sum(case when feature_code = 'sales' then target_value else 0 end ) sales,
sum(case when feature_code = 'retailCount' then target_value else 0 end ) retail_count,
sum(case when feature_code = 'atv' then target_value else 0 end) atv,
sum(case when feature_code = 'storeCustomerCnt' then target_value else 0 end) store_customer_cnt,
sum(case when feature_code = 'upt' then target_value else 0 end) upt,
sum(case when feature_code = 'cr' then target_value else 0 end) cr,
sum(case when feature_code = 'md' then target_value else 0 end) md
from (
select
sub.code feature_code,
sum(sub.value::numeric) target_value
from
(
select
json_array_elements(fetch_target_json) ->> 'featureCode' code,
json_array_elements(fetch_target_json) ->> 'targetValue' value
from
t_sc_feature_daily_target_info
where
tenant_id = $tenant_id$
and rel_type = 'STORE'
and rel_id in ($storeIds$)
and stats_date between $startDate$ and $endDate$) sub
group by sub.code order by sub.code
) sub2

建表 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE "public"."t_task_center_main" (
"id" SERIAL NOT NULL,
"url_json" json,
"creator_id" int4,
"rel_scorecard_id" int4,
"finish_value" numeric(10,4),
"is_tree_leade" bool DEFAULT false,
"content" text COLLATE "pg_catalog"."default",
"evaluation" varchar(255) COLLATE "pg_catalog"."default",
"date_created" timestamp(6),
"last_modified" timestamp(6),
CONSTRAINT "t_task_center_main_pkey" PRIMARY KEY ("id")
);

COMMENT ON COLUMN "public"."t_task_center_main"."id" IS '主键id';
COMMENT ON COLUMN "public"."t_task_center_main"."date_created" IS '创建时间';
COMMENT ON COLUMN "public"."t_task_center_main"."last_modified" IS '修改时间';

COMMENT ON TABLE "public"."t_task_center_main" IS '主任务表';