posgre基本内置函数

目录

• 菜鸟教程
• 长度-length
• 替换-replace
• 字符串分割
• 字符所在下标位置-position
• 字符串截取-substring
• md5
• 时间戳转换为标准时间
• json内部查询mysql
• json内部增删改
• posgre内部json操作
• 设置主键自增
• json接收，数据库jsonb
• postgresql执行计划
• 死锁操作
• 错误记录
  • 1.pq: duplicate key value violates unique constraint

菜鸟教程

https://www.runoob.com/postgresql/postgresql-functions.html

点击跳转到菜鸟教程

长度-length

select length('aa-bb-cc') // 8

替换-replace

// '-'替换为''
select replace('aa-bb-cc','-','') // aabbcc

字符串分割

-- 字符串被分成3部分，取最后一部分，那最后一个参数就是3
select split_part('aa-bb-cc' ,'-', 3)  // cc

字符所在下标位置-position

select  position('f' in 'abcdef') // 6

字符串截取-substring

select SUBSTRING('abcd',2); // bcd，下标从1开始，从2截取到末尾
select substring('abcd',2,1); // b,下标从1开始，从2截取1个字符

md5

SELECT md5('abc') // 900150983cd24fb0d6963f7d28e17f72

时间戳转换为标准时间

SELECT to_timestamp(1634634710) //2021-10-19 09:11:50+00

json内部查询mysql

SELECT JSON_EXTRACT(json_str, '$.name') 
select JSON_EXTRACT('{"nickname": "goodspeed", "avatar": "avatar_url"}','$.nickname')

json内部增删改

// 修改json内部单个字段
update tablename set aaa = aaa::jsonb || '{"isbool": false}'::jsonb where id = '999'


// 搭配使用,如果传过来的aaa='{}',那么就不做修改。否则传过来的值做修改
update tablename
   set bbb = 1
      ,aaa = case when $1::character varying = '{}' then aaa::jsonb || '{}'::jsonb else  aaa::jsonb || $1::jsonb end
 where user_id = 888
   and id = 999

// json整体修改
update tablename set aaa = '{"aaa": "bbb"}'::jsonb where id = '999'

update user_bike_map set security_custom = security_custom::jsonb || '{"quietDefence": false}'::jsonb

posgre内部json操作

简单查询

# 存储的是key-value格式的数据，通过指定的key获取对应的值
# 使用->返回的结果是带引号的
select '{"nickname": "goodspeed", "avatar": "avatar_url"}'::json->'nickname' as nickname;
# 使用->>返回结果不带引号
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' as nickname;

复杂查询

# {tags,0}代表查询key=‘tags'，且value为数组中数组的第一个元素
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' as tag;
# {aa,b,0}代表查询key=‘aa'，对应的value中，key=‘b'的value中，数组的第一个元素
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"], "aa":{"b": [{"c": 1}]}}'::jsonb#>>'{aa,b,0}';

修改

格式：jsonb_set(原数据, 需要更改的位置, 替换的值，是否不存在时新增)
select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);

设置主键自增

serial primary key:主键自增

CREATE TABLE tablename (
  "id" serial primary key
);

json接收，数据库jsonb

&json.RawMessage{}
type data struct {
	jsonData        json.RawMessage `json:"jsonData"` 
}

postgresql执行计划

explain analyze sql

explain analyze SELECT * FROM alert_log where alert_id = 'H2208091618000016%'
explain analyze SELECT * FROM alert_log WHERE ts =1660033091

输出解释：

输出解释：
Seq Scan on alert_log  (cost=0.00..1683.10 rows=2 width=183) (actual time=11.641..11.748 rows=1 loops=1)

Index Scan using index_alert_log_alert_id on alert_log  (cost=0.29..8.31 rows=1 width=183) (actual time=0.032..0.034 rows=1 loops=1)

Index Scan 用到了索引
Seq Scan 没有用到索引
rows 返回行数
loops 执行了1次
Execution 用时时间

死锁操作

select oid from pg_class where relname='可能死锁的表名';  // 可能死锁的表

select pid from pg_locks where relation='oid';   // 通过oid查询到pid

select pg_cancel_backend(pid); // 正常情况下清理死锁
select pg_terminate_backend(pid); // 顽固的pid 需要用的高级语句

错误记录

1.pq: duplicate key value violates unique constraint

pq: duplicate key value violates unique constraint "table_name_pkey"
出现这个问题的原因是大批量插入数据(先清空数据后又导入数据)导致表tablename和tablename_id_seq不一致造成的,从下面的两个sql语句可以看出，前面的值与后边的值不一样
select max(id) from tablename;
SELECT nextval('tablename_id_seq');
解决方法：同步写自增序列
select setval('table_name_id_seq',(select max(id) from table_name));