postgresql的使用

本文主要是介绍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

这篇关于postgresql的使用的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！