pg数据库sql使用遇到的问题及解决办法

该文章主要内容

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获取随机数