当前位置:   article > 正文

pgsql常用sql和函数_pgsql 文档

pgsql 文档
  • 常用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类型等。

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

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

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

  1. regexp_split_to_array(col,',')

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

  1. string agg

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

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

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

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

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

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

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

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

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

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

参数表示截取的长度。

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

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

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

  1. 将日期转换成INTERVAL格式

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

  1. row_number() over 函数

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

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

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

  1. 查询表占用大小

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

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

清空指定表:TRUNCATE TABLE tablename

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

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

  1. count中添加条件

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

  1. 查询表中:指定字段有重复的记录

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

  1. 字符连接函数

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

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/136809
推荐阅读
相关标签
  

闽ICP备14008679号