postgresql的使用
1. 字段拼接，分组特殊用法
SELECT send_org_code || receive_org_code FROM rst.rst_ra_sku_store_package_info GROUP BY send_org_code || receive_org_code
2. GREATEST和LEAST
GREATEST(value [, ...])
LEAST(value [, ...])
# 注意比较值得类型一定要相同
案例：比较time1，time2, time3 三个时间大小
-- drop table biztable
create table biztable (
id int PRIMARY key,
time1 TIMESTAMP,
time2 TIMESTAMP,
time3 TIMESTAMP
);
-- truncate table biztable
select * from biztable;
insert into biztable VALUES(1,'2018-05-20 22:52','2019-05-20 22:54','2019-05-20 23:52');
select id,GREATEST(time1,time2,time3)as maxval, LEAST(time1,time2,time3) AS minval
from biztable;
GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。 这些表达式必须都可以转换成一个普通的数据类型，它将会是结果类型 （http://www.postgres.cn/docs/9.6/typeconv-union-case.html）。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。
请注意GREATEST和LEAST都不是 SQL 标准，但却是很常见的扩展。某些其他数据库让它们在任何参数为 NULL 时返回 NULL，而不是在所有参数都为 NULL 时才返回 NULL。
3. nullif
NULLIF(value1, value2)
当value1和value2相等时，NULLIF返回一个空值。 否则它返回value1。
4. COALESCE
COALESCE(value [, ...])
COALESCE函数返回它的第一个非空参数的值。当且仅当所有参数都为空时才会返回空。它常用于在为显示目的检索数据时用缺省值替换空值
5. case
SQL CASE表达式是一种通用的条件表达式，类似于其它编程语言中的 if/else 语句。
6. SELECT 4/NULL
--结果为NULL不会报错
7. count()获取记录数
SELECT count(1) OVER(PARTITION by product_class) FROM rst.rst_ra_store_comparison
--OVER(PARTITION by product_class)表示按product_class字段分类计数
8.创建数据，冲突时更新
INSERT INTO rst.rst_ra_store_remark
(store_code, dec_day_date, remark)
values
('1001', '2021-1-1', '备注信息：789')
ON CONFLICT (store_code, dec_day_date) -- 表必须有store_code, dec_day_date的唯一索引，否则报错
DO UPDATE SET remark=EXCLUDED.remark;
9 pg中的with递归的使用：
WITH RECURSIVE included_parts(sub_part, part) AS (
SELECT sub_part, part FROM parts WHERE part = ’our_product’
UNION ALL
SELECT p.sub_part, p.part
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
DELETE FROM parts
WHERE part IN (SELECT part FROM included_parts);
10. pg中字段组装为json: json_build_object()
select json_build_object('option_name', option_name, 'option_value', option_value, 'serial_number', serial_number) as op
from tenant_1888888888_rst.rst_ra_store_ov_filter_dict
where tree_type = '森马'
得到的json:
{"option_name" : "森马", "option_value" : "森马", "serial_number" : 1}
多个json对象组装为json数组：json_agg()
select field_name,
json_agg(json_build_object('option_name', option_name, 'option_value', option_value, 'serial_number', serial_number)
order by serial_number asc) as options
from tenant_1888888888_rst.rst_ra_store_ov_filter_dict
where tree_type = '森马'
group by field_name;
11. string_agg, 多个值拼接
SELECT u.user_id, u.username, u.name, string_agg(r.role_name, ',') AS role_name --多个值拼接，按逗号分隔
FROM {system_schema_name}.user u LEFT JOIN {system_schema_name}.user_role ur ON u.user_id = ur.user_id
LEFT JOIN {system_schema_name}.role r ON ur.role_id = r.role_id
WHERE u.is_delete = 'f' {user_id_condition}
GROUP BY u.user_id
11.分组后结果集转数组：array_agg()
SELECT DISTINCT module, array_agg(page) pages
FROM t
GROUP BY module
12.postgresql中同一条没有order by的sql多次执行得到的结果集的顺序是不固定的，要想每次查询结果顺序一致，必须按唯一键排序
pg中order by 语法：
ORDER BY time DESC, l.id ASC --先按时间倒序，再按id正序，默认正序
13. bool_or()讲布尔值转化为t或者f
SELECT bool_or(false); --f
SELECT bool_or(true); --t
14.字段的截取
SELECT substr(name, 1, 3) FROM t1 WHERE id = 1 --截取name字段第一个字符到第三个字符，如name值为“abcde” 则查到的为abc
15.jsonb字段先转text，再转int
select (store -> 'total')::text::int+ (store2 -> 'total')::text::int AS total_sum from 。。。
16. 按部分字段去重：
SELECT DISTINCT ON(code, name) code, name, store FROM t1 -- 按code, name去重并显示code, name, store
17.数组类型字段的操作
- 数组类型的声明：
"exception_time" timestamp(0)[],
2.查询，
SELECT exception_time FROM table_name
{"2021-06-02 00:00:00","2021-06-09 00:00:00"}
3.根据下标取数组中元素： --下标从1开始
SELECT exception_time[2] FROM table_name
2021-06-09 00:00:00
18.update语句和from结合使用
UPDATE public.t1 set "name" = t2.name FROM public.t2 WHERE t2.id = 2 and t1.id = 2
说明更新t1中id为2的记录的name字段值为t2表中id为2的name字段值
19 insert语句中使用select
1.将select的查询结果集直接插入：
INSERT INTO "public"."t2" ("id", "name") SELECT id, name FROM public.t1 WHERE id = 6
2.使用select查询某个字段的值
with t as ( SELECT name from public.t1 WHERE id = 2 ) INSERT INTO "public"."t2" ("id", "name") VALUES (8, (SELECT name from t))
这里插入时候的name值从public.t1表中获取，这种情况下， SELECT name from t 语句必须返回单个值，否则报错
20 any和数组的使用
查询year 存在于指定数组【2018,2019】的记录:
SELECT year FROM public.date WHERE year = ANY(ARRAY[2018,2019])
查询year 不存在于指定数组【2018,2019】的记录:
SELECT year FROM public.date WHERE NOT year = ANY (ARRAY[2018,2019]) ORDER BY year
21 concat_ws数据字段拼接
. 一条记录数据字段拼接
语法：concat_ws('拼接符号',字段名,more fields)
例子：concat_ws(':',username,sex)
SELECT concat_ws('_', name, age), code FROM "t1"
22 jsonb类型使用：
->符号取出来的数据类型为jsonb
->>符号取出来的数据类型为text
(stock_qty->'total')::int --报错，jsonb无法转换为int
(stock_qty->>'total')::int --不报错
23 case when then使用中的坑：
假设有t1表，表中qty字段类型为int类型，现在有以下语句：
UPDATE t1 SET qty = ( CASE WHEN order_id = 'de8ba3a5-8d2f-4c7c-888a-6777b49b1adb' THEN null WHEN order_id = 'd54c518d-47ab-4af1-8bf7-7dd751c79c72' THEN null END ) WHERE id ='de8ba3a5-8d2f-4c7c-888a-6777b49b1adb' or id = 'd54c518d-47ab-4af1-8bf7-7dd751c79c72'
上面更新语句会报错：
> ERROR: column "human_ra_qty" is of type integer but expression is of type text
LINE 4: CASE WHEN order_id = 'de8ba3a5-8d2f-4c7c-888a-6777b49b1a...
修改sql语句为：
UPDATE t1 SET qty = ( CASE WHEN order_id = 'de8ba3a5-8d2f-4c7c-888a-6777b49b1adb' THEN null WHEN order_id = 'd54c518d-47ab-4af1-8bf7-7dd751c79c72' THEN 1 END ) WHERE id ='de8ba3a5-8d2f-4c7c-888a-6777b49b1adb' or id = 'd54c518d-47ab-4af1-8bf7-7dd751c79c72'
执行成功！
猜测上面错误原因：case语句返回值类型根据then后面的值决定，当有一个null一个int时，case表达式返回int，当都为null时，默认返回值类型为text,因此出现上面的错误，解决方案：进行类型转换：
UPDATE t1 SET qty = ( CASE WHEN order_id = 'de8ba3a5-8d2f-4c7c-888a-6777b49b1adb' THEN null WHEN order_id = 'd54c518d-47ab-4af1-8bf7-7dd751c79c72' THEN 1 END )::int WHERE id ='de8ba3a5-8d2f-4c7c-888a-6777b49b1adb' or id = 'd54c518d-47ab-4af1-8bf7-7dd751c79c72'
