Postgresql项目中常用细节

Postgresql之split_part()切割函数

 

split_part(string text, delimiter text2, field int)

text----要切割的字段

text2----按照什么形式切割

int----截取的位置

 

eg:

text=“name.cn” split_part(text,’.’,1) 结果： name

text=“name.cn” split_part(text,’.’,2) 结果： cn

text=“name.cn.com” split_part(text,’.’,3) 结果： com

 

 

批量更新

UPDATE het_contract
SET contract_year = tmp.contract_year
FROM
    (
        SELECT
            contract_no,
            cast(split_part(contract_no, '-', 3) as INTEGER) AS contract_year
        FROM
            het_contract
    ) AS tmp (contract_no, contract_year)
WHERE
    het_contract.contract_no = tmp.contract_no;

 

PostgreSQL 踩坑系列 关于to_date()

to_date(text,text)， 在PostgreSQL 里 也是将字符串转换成日期， 但是 仅仅是年月日部分，就算我们在方法里指定了格式亦是如此

比如： select to_date('2019-01-15 18:33:41','yyyy-MM-dd hh24:mi:ss');

结果就是   2019-01-15

因此如果在项目里使用 这函数去对日期操作，比如 查询某日某时某点某秒之前的访问数据量，当入参正确，那么计算很有可能是错误的。因为时分秒没了，会自动隐式转换成 2019-01-15 00:00:00。和这个数据比较，那么15号这天的数据都是不存在的。

而正确的使用姿势应该是：

select to_timestamp('2019-01-15 23:59:59','yyyy-MM-dd hh24:mi:ss')

因此，在使用PostgreSQL进行日期相关操作的时候如果没有特殊要求尽量使用to_timestamp(text,text) 方法去进行操作。

 

PostgreSQL 根据ID查找它全部的父级

with RECURSIVE cte as
(
select a.id,a.value,a.parent_id from {table_name} a where a.id={id}
union all  
select k.id,k.value,k.parent_id from {table_name} k inner join cte c on c.parent_id = k.id
)select id,parent_id,value from cte order by id

 

PostgreSQL 直接在查询结果中生成唯一ID

不需要排序

select (row_number() over()) as id from generate_series(1,100)

 

根据指定的字段排序

select (row_number() over(order by xxx desc)) as id , xxx from generate_series(1,100) as xxx

 

PostgreSQL 中如何实现group_concat

之前在MySQL中使用group_concat，觉得超级好用。

今天在PostgreSQL需要用到这样的场景，就去学习了一下。

在PostgreSQL中提供了array_agg的函数来实现聚合，不过返回的类型是Array。

如果我们需要得到一个字符串类型的数据时，可以通过 array_to_string(array_agg(字段),',')方式来实现。

当然也有更加方便的函数来实现，string_agg(字段,',')。

 

PostgreSQL 主键自增

postgreSQL 自增需要使用序列

1.使用SERIAL

自动创建名为users_id_seq的序列，且MAXVALUE=9223372036854775807，初始值为1

2.先创建序列，然后设置字段的自增

 

增加INT字段

alter table [tableName] add column [filed] int4;