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 '主任务表';