关于PostgreSQL数据增删改查的日常总结（主要jsonb类型）

        在工作中，对PostgreSQL数据库操作，最难的也就是对jsonb类型的数据进行增删改查了，其他字段跟MySQL数据库没什么区别，现在我就分享一下平时工作中总结的相关操作，这是我承包公司一年sql脚本开发中遇到并总结的，公司使用这种数据库的可以收藏，提高你的开发速度。注意,示例中ext为jsonb类型。

一·、jsonb类型更新语法

（1）普通更新-方式一

UPDATE tenant_data_record
SET ext = jsonb_set (
  ext,
  '{update_category}',
  '2'
)

（2）普通更新-方式二

UPDATE tenant_data_record
SET ext = jsonb_set (
	ext,
	'{place}',
	'"北京"' :: jsonb
)
WHERE
	ID = 7903091958494211

（3）普通更新方式三

UPDATE tenant_data_record
SET ext = ext - 'place' || '{"place":5}'
WHERE ID = 7903091958494211

（4）更新字段为null

update tenant_data_record set ext
=jsonb_set(
ext,
'{type}',
'null'::jsonb)
 where id=7903091958494211

（5）更新字段为空，单引号和双引号要注意。

update tenant_data_record set ext
=jsonb_set(
ext,
'{type}',
'""')
 where id=7903091958494211

（6）更新一张表中的jsonb类型中一个字段为另一张表jsonb类型中一个字段

update customer_product SET ext = jsonb_set (customer_product.ext, '{team}', 
(select user_info.ext->'team' from user_info  where 
customer_product.create_by =  user_info.id::text))

二、新增jsonb字段操作

（1）jsonb里面还不存在该字段，使用更新方式就相当于添加字段

UPDATE tenant_data_record
SET ext = jsonb_set (ext, '{cplace}', '"北京"')
WHERE
	ID = 7903091958494211

（2）添加多个字段

UPDATE tenant_data_record
SET ext = ext - 'place' || '{"place":1,"place2":4}';

三、查询相关操作

（1）当开发拿不到权限时，查询表结构

select * from information_schema.columns
where  table_name = 'opportunity'

（2）以json格式查询出jsonb数据

SELECT
	jsonb_pretty (ext)
FROM
	tenant_data_record
WHERE
	ext ->> 'type' = '2'
AND ID = 7903091958494211

（3）查询jsonb数据的类型

select  jsonb_typeof(ext->'shared_owner')  from customer

（3）查出来的数据时间戳转为日期

 to_timestamp("coach_feedback".confirm_time / 1000) AT TIME ZONE 'PRC' AS 确认时间

（4）将两个jsonb类型字段求和查询出来，值为空时赋值默认值0

coalesce(ext->>'ward_potential','0')::NUMERIC+coalesce(ext->>'ward_potential','0')::NUMERIC 

（5）查询出当月五号

 select DATE(to_char(now(),'yyyy-MM-05'))

（6）身份证在excel显示中后四位为0，前面加引号，防止显示错误，引号前面加引号·进行中转义，所以看是有四个引号。

concat('''',c.id_number) 

（7）查询出来的时间转时间戳显示

select  extract(epoch from to_timestamp('2020-03-27 14:55:59', 'YYYY-MM-DD HH24:MI:SS')) * 1000      

（8）查询返回的jsonb数据带引号

c.ext->'hco_name'

（9）查询返回的jsonb数据不带引号，以文本返回，使用->>操作符

ext->>'score_date'

（10）根据数据库里面字段值替换不同值

 CASE
WHEN ext->'has_authorized'::text = 'true' THEN
	'是'
ELSE
	'否'
END 是否已授权,

（11）查询出来为空，赋值默认值

select COALESCE(b.price, 0) as price from fruit_sale b

三、where条件组装

（1）将array类型的jsonb数据转化为in条件进行筛选，查询结果用逗号分隔。

	SELECT
		string_agg (NAME, ',')
	FROM
		user_info u
	WHERE
		u. ID IN (
			SELECT
				jsonb_array_elements_text (C .ext -> 'shared_owner') :: int8
		)
) AS 负责人

（2）组装条件为当月五号之前

where DATE (
	to_char(
		to_timestamp(mr."month" / 1000) + INTERVAL '8 hours',
		'yyyy-MM-dd'
	)
) <= DATE (to_char(now(),'yyyy-MM-05'))

（3）日期比较

 DATE (
	to_char(
		to_timestamp(tot.start_date / 1000) + INTERVAL '8 hours',
		'yyyy-MM-dd'
	)
) <= DATE ('2019-09-10')

四、总结

    以上就是我关于PostgreSQL数据增删改查的日常总结，欢迎关注微信公众号java基础笔记，欢迎点赞支持。