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.数组类型字段的操作

1. 数组类型的声明： 

　　"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'