pgsql中的常用函数总结

ps： over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行

1、 这样的结果是先按照stationcode分组，再按照时间进行排序。第一列为序列号

select row_number() over(partition by stationcode ORDER BY time),stationcode,time from dcsrealtime

2、按照stationcode进行分组后再按照time排序，然后筛选出rank为1的记录

select * from (select row_number() OVER(partition by stationcode ORDER BY time) rank,* from dcsrealtime rank) temp where temp.rank='1'

3、查询表占用大小

    查询占用字节：select  pg_relation_size('tablename');
    查询占用MB：select pg_size_pretty(pg_relation_size('tablename'));
    清空指定表：TRUNCATE TABLE tablename

4、将时间戳转为指定格式

select to_char(to_timestamp(updatetime/1000),'yyyy-MM-dd HH:MI:SS') from dangersource

5、空值替换函数（mysql使用ifnull，pgsql使用coalesce）

select level,coalesce(level,'蓝') from alarms

6、count中添加条件

select count(字段名 LIKE '%true%' or null) as 字段名 from tablename

7、查询表中：指定字段有重复的记录

select * from organization where code in (select code from organization GROUP BY code HAVING count(code)>1) ORDER BY name
 

8、字符连接函数

-- pgsql的三种字符链接函数，将多列的字段连接成一条数据
-- arrary_agg （）  string_agg( )  xmlagg()  分别来处理数组，字符串和xml文档。 

-- array_agg字符链接后是json格式
SELECT array_agg(name ORDER BY name DESC) FROM monitoritem GROUP BY dangersourceid;    
-- string_agg可以指定分隔符
SELECT string_agg(name, ',' ORDER BY name) FROM monitoritem GROUP BY dangersourceid;

-- ps：mysql的字符链接函数
select d.name,wm_concat(m."name") as roleName from dangersource d join monitoritem m on d.id = m.dangersourceid

9、pgsql区别于mysql的单字段去重

mysql查询数据想根据某个字段去重可以使用group by，pgsql不支持，但是支持：select distinct on(字段) * from 表。切记要用order by进行排序，否则会导致去重后返回的数据不明确！

10、mysql中的修改GROUP_CONCAT默认长度

永久设置：
C盘下面输入：%programdata%
在my.ini中加：group_concat_max_len = -1
临时设置：
SET SESSION group_concat_max_len=1024000000;
SET GLOBAL group_concat_max_len=102400000;
查看：
SELECT @@global.group_concat_max_len;
show variables like "group_concat_max_len";

11、Pgsql备份数据库的快捷方式

  语法：-h:端口，-U：pg用户名，-d：数据库 D:\数据库备份\databasename.bak

备份：
pg_dump -h 192.168.31.212 -U postgres ruoyipg> D:\数据库备份\databasename.bak

还原：
psql -h localhost -U postgres -d ruoyipg< D:\数据库备份\databasename.bak