关于PostgreSQL数据增删改查的日常总结（主要jsonb类型）
关于PostgreSQL数据增删改查的日常总结（主要jsonb类型）
jiankang666
发布于 2022-05-12 21:01:25
发布于 2022-05-12 21:01:25
在工作中，对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数据增删改查的日常总结
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-04-14，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！