pgsql常用sql和函数

• 常用pgsql

-- 列出所有schema

select * from information_schema.schemata;

-- Schema下所有表

select * from pg_tables where schemaname = 'query_db' and tablename in('port','device','res_carry_business','hardware','shelf','device_hardware_relation');

-- Schema下所有索引

select * from pg_indexes where schemaname = 'query_db' and tablename in('port','device','res_carry_business','hardware','shelf','device_hardware_relation');

-- Schema下所有视图

select * from pg_views where schemaname = 'trans_con_db';

-- schema下所有序列

select * from pg_sequences where schemaname = 'bs_res_sc' limit 10;

-- Schema下所有函数 (*有很多信息)

SELECT distinct routines.specific_schema,routines.routine_name

FROM information_schema.routines

JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name

WHERE routines.specific_schema='trans_con_db';

-- 查询resource_db下query_db下，所有函数以及过程

select routine_name,* from information_schema.routines where routine_catalog = 'resource_db' and routine_schema = 'query_db';

-- 表被引用

select *

-- routine_name,routine_definition,routine_type

from information_schema.routines

where routine_definition like '%business_channel%'

order by routine_type;

-----------------------------------------------------------------------------------

-- 添加字段

alter table coll_5gc_number add column network_role_id numeric;

-- 修改表字段not null改为null

alter table coll_5gc_number alter code drop not null;

-- 修改表字段为not null

alter table coll_5gc_number alter code set not null;

-- 删除表字段

alter table bas_cm_tenant_verification drop column if exists verify_type;

-- 新增主键

alter table query_db.device add CONSTRAINT device_pkey primary key(device_id);

---修改schema的属主

alter schema test owner to highgo;

-- 修改表的属主

ALTER TABLE "query_db"."wroute_seg_num" OWNER TO "postgre";

-- 重命名表

alter table project_manage_log_20230310 rename to project_manage_log_20230310_yzy;

-----------------------------------------------------------------------------------

-- 添加唯一索引

CREATE UNIQUE INDEX device_device_id_pkey ON query_db.device USING btree (device_id);

-- 添加索引

CREATE INDEX idx_device_group_type ON query_db.device USING btree (group_type);

-----------------------------------------------------------------------------------

-- 创建备份表，并将主表CHECK约束、主键约束 和索引约束、注释、默认值一起备份（顺序不要错）

create table project_manage_log_20230310

(like project_manage_log including constraints including indexes including comments including defaults);

-- 主表数据写入备份表（顺序不要错）

insert into project_manage_log_20230310 select * from project_manage_log;

-----------------------------------------------------------------------------------

grant select on all tables in schema unit_sc to pg5gc_control_user; -- 赋予用户所有表的查询权限

grant all on all tables in schema unit_sc to pg5gc_control_user ; -- 赋予用户所有表的权限

grant all on all sequences in schema cnrd_portal_sc to unit_user; -- 赋予用户所有序列的权限

grant usage on schema unit_sc to pg5gc_control_user; -- 赋予sechma的使用权限

grant select,update,insert,delete on table unit_sc.cm_device to pg5gc_control_user; -- 赋予用户单表crud权限

revoke select,update,insert,delete on unit_sc.cm_device from pg5gc_control_user; -- 删除用户权限

-----------------------------------------------------------------------------------

create user username; -- 创建用户，默认拥有登录权限

create role username; -- 创建角色，默认没有登录权限

create role username with login password '123456'; -- 创建角色并设定密码和登录权限；

alter role username with nologin; --禁止角色登录

-----------------------------------------------------------------------------------

• 常用pgsql函数

1. COALESCE(col,value);

COALESCEI函数的作用是空值替换函数，即当某列的值为空时，则可用其他值进行替换，再返回给前端。

eg:COALESCE(totalnum,O);即当字段名为totalnuml的列的值为空时，则用0进行替换，进

行替换的值可根据实际情况可用number类型，也可用varchar类型等。

2. regexp split to table(col,',');

regexp split to_table函数的作用是利用正则表达式将字符串分割成表格。即将对指定列的值

进行分割，分割后的每个子串将转成一行，多个子串将转成多行。如下图所示：

3. regexp_split_to_array(col,',')

regexp_split to_array是将某一字段的值以特定的符号进行分割后转换为数组的格式，入下图所示

4. string agg

直接把一个表达式或者某一列的字段合并变成字符串

--string agg(expression,delimiter order by expression)

第一个参数表示需要合并的字段或者表达式。

第二个参数表示以什么符号进行将第一个参数进行链接起来，一般都是使用'，'。

第三个参数为可选项，表示将第一列的字段以怎样的排序进行合并。

示例：string agg(f b getusername(r.userid),',' order by r.crt time desc)as mjxms

注：第二个参数与第三个参数之间不需要逗号进行隔离，只需要空格就行，甚至不需要空格！

5. substring(string[from int][for int])

substring('abcdefg'from 2 for 3)=bcd

第一个参数是需要被截取的字符串，第二个参数是从第几位开始截取，起始位置是1，第三个

参数表示截取的长度。

6. 获取当前日期是星期几

SELECT extract (dow FROM cast (now (as TIMESTAMP))

将dow换成day就是获取当前日期是当前月份的第几天

7. 将日期转换成INTERVAL格式

select age(2019-10-10','2018-04-19) 可计算两个日期之间相差多少日期

8. row_number() over 函数

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

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

9. 按照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'

10. 查询表占用大小

查询占用字节：select pg_relation_size('tablename');

查询占用MB：select pg_size_pretty(pg_relation_size('tablename'));

清空指定表：TRUNCATE TABLE tablename

11. 将时间戳转为指定格式

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

12. count中添加条件

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

13. 查询表中：指定字段有重复的记录

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

14. 字符连接函数

-- 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

15. pgsql区别于mysql的单字段去重

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

• pagsql文档参考

逻辑操作符，比较操作符，数学函数和操作符，字符函数和操作符，位串函数和操作符

https://www.cnblogs.com/orangeform/archive/2012/05/02/2294071.html

模式匹配，数据类格式化函数，时间/日期函数和操作符

https://www.cnblogs.com/orangeform/archive/2012/05/04/2294643.html

序列操作函数，条件表达式，数组函数和操作符，系统信息函数，系统管理函数

https://www.cnblogs.com/orangeform/archive/2012/05/07/2295273.html