pg数据库sql使用遇到的问题及解决办法
- 该文章主要内容
- 1 pgsql 使用with 方法实现merge方法
- 2 postgresql多表联合批量更新
- 3 Postgresql 类似oracle的decode
- 4 如何将当前行与PostgreSQL中的下一行和上一行进行比较
- 5 postgresql使用with 实现merge方法实现有数据则更新,无数据插入
- 6 @JsonIgnore返回结果忽略默写字段,请求又不想忽略这些字段
- 7 postgres的null值替换函数
- 8 SQL按照某个字段的内容进行分组,并在组内加序号
- 9 如何使用postgres将间隔转换为数小时
- 10 postgresql中没有dual表解决方案
- 11 Postgresql数据库获取子串下标位置和正则截取
- 12 postgresql获取随机数
该文章主要内容
1 pgsql 使用with 方法实现merge方法
具体语法如下:
with “别名” as (update 表名 set 字段名1= ‘更新的值’,字段名2=‘更新的值’ where 主键=需要更新的主键的值 returning *)
insert into 表名(字段名1,字段名2) select ‘更新的值’,‘更新的值’ where (select count(*) from 别名) = 0
create table test(
id serial primary key,
aa varchar(10),
bb varchar(10)
);
insert into test(aa,bb) values('qw','rt');
insert into test(aa,bb) values('vb','ad');
insert into test(aa,bb) values('er','fg');
with "te" as (update test set aa = 'test',bb='test' where id =4 returning *)
insert into test(aa,bb) select 'test','test' where (select count(*) from te) = 0
2 postgresql多表联合批量更新
update table_p as p
set p_name = a.name,p_user = a.user_id,p_type = 'P02'
from (
select x.user_id,x.name,x.p_id from table_u x
join table_p y on x.p_id = y.p_id
) as a where p.p_id = a.p_id;
参考文章:
postgresql多表联合批量更新
3 Postgresql 类似oracle的decode
postgresql的数据库没有类似decode的方法,可以使用case when代替。
select (case when order_count = 0 then 1 else order_count end)
as ordercount
from order
case when 当数据order_count是0我们把ordercount结果设置成1,如果不是零我们就取order_count的值。
4 如何将当前行与PostgreSQL中的下一行和上一行进行比较
该功能需要使用 WINDOW和lag或者lead函数实现
具体用法如下:
例
假设我的数据库中有两个属性(有序位置和随机数),我想要检索偶数之间的奇数.我该怎么做?
真正的用法
我想要找到具有类别NAME(而且这个词不是名字)的另外两个单词之间的单词.排序由句子和位置提供.
SELECT text
FROM (
SELECT text
,category
,lag(category) OVER w as previous_cat
,lead(category) OVER w as next_cat
FROM token t
JOIN textblockhastoken tb ON tb.tokenid = t.id
WINDOW w AS (PARTITION BY textblockid, sentence ORDER BY position)
) tokcat
WHERE category <> 'NAME'
AND previous_cat = 'NAME'
AND next_cat = 'NAME';
还有其他的用法可以参考以下参考文章
参考文章:
如何将当前行与PostgreSQL中的下一行和上一行进行比较
LEAD函数在数据分析中的应用
Oracle SQL之lag()和lead()函数使用
5 postgresql使用with 实现merge方法实现有数据则更新,无数据插入
参考文章:
数据库常用函数DECODE()、LAG()、LEAD() 基础用法
6 @JsonIgnore返回结果忽略默写字段,请求又不想忽略这些字段
@JsonIgnore 注解是用来注解那些在后台返回时 用来屏蔽某些字段的,如在返回user实体时,就不想返回data字段,,只需要在这个字段的get方法上加上@JsonIgnore 注解即可
如图
但是问题来了,当在后台进行数据库操作或者使用这个方法获取字段就获取不到值,报空指针,出现在这个原因也是因为我们再data上加上@JsonIgnore 字段导致的
那么有没有一种方法既可以满足响应有可以满足请求呢?答案肯定是有的
如图:
在这里插入图片描述
我们只需要在对应的get,set方法上分别加上这两个注解即可:@JsonIgnore 返回时忽略,@JsonProperty 请求时注入
就能满足我们的需求了
7 postgres的null值替换函数
COALESCE(e.id, t.code) AS "id"
或者
COALESCE(a.num, 0) as floor_num
注意如果num为vachar类型,默认值0需要改为'0'
8 SQL按照某个字段的内容进行分组,并在组内加序号
例如:
种类 规格
A 100*100
A 100*50
B 50*50
B 100*200
B 100*100
C 100*100
加序号之后,变成
序号 种类 规格
1 A 100*100
2 A 100*50
1 B 50*50
2 B 100*200
3 B 100*100
1 C 100*100
SQL代码如下:
SELECT ROW_NUMBER() OVER (PARTITION BY TABLE.种类 ORDER BY TABLE.种类) ,种类,规格 FROM TABLE
其中PARTITION BY 是对数据进行分组,ORDER BY是对数据进行排序。
参考文章:
SQL按照某个字段的内容进行分组,并在组内加序号
9 如何使用postgres将间隔转换为数小时
如果你想要整数即天数:
SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int
如果你想要整数即小时:
SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/3600)::int
10 postgresql中没有dual表解决方案
with info AS (
select '小王' AS "name", '25' AS "age" union all
select '小李' AS "name", '26' AS "age"
)
select * from info
11 Postgresql数据库获取子串下标位置和正则截取
11.1 获取子串在一字符串中的位置
函数:position(substring in string)
说明:Location of specified substring 子串在一字符串中的位置
例子:select position(‘ma’ in ‘pmars’); = 2
11.2 利用正则表达式对一字符串进行任意长度的字串的截取
函数:substring(string from pattern)
说明:Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. 利用正则表达式对一字符串进行任意长度的字串的截取
例子:select substring('topmars' from 'p.*$'); = "pmars"
函数:substring(string from pattern for escape)
说明:Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching. 利于正则表达式对某类字符进行删除,以得到子字符串
例子:select substring('Thomas' from '%#"o_a#"_' for '#'); = "oma"
参考文章:
Postgresql数据库的一些字符串操作函数
12 postgresql获取随机数
12.1 获取0 - 1之间的随机小数
select random();
12.2 获取一个1 - 10000之间的随机整数,ceil函数:得到不小于参数的最小的整数,floor:得到不大于参数的最大整数,trunc:截断
SELECT ceil(random()*(10000-1)+1) as num;
SELECT floor(random()*(10000-1)+1) as num;
SELECT trunc(random()*(10000-1)+1) as num;
参考文章:
postgresql获取随机数