postgre常用函数

pg查进程

--pg库查进程
select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (
    select pid from pg_locks l
                        join pg_class t on l.relation = t.oid
        and t.relkind = 'r'

);

--pg杀进程
SELECT pg_terminate_backend(pid);

pg查看表占磁盘大小

--查看所有表所占磁盘空间大小
select sum(t.size) from (
SELECT table_schema || '.' || table_name AS table_full_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
) t


--查看每个表所占用磁盘空间大小
SELECT table_schema || '.' || table_name AS table_full_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

orcle转pg

CHAR(n)------>CHAR(n)
VARCHAR2(n)------>varchar(n)
NUMBER(n, m) ----->NUMERIC(n, m)
NUMBER(4)----->SMALLINT
NUMBER(9)----->INT
NUMBER(18)----->BIGINT
NUMBER(n)----->NUMERIC(n) --如果n大于19，则可以转换为numeric类型
DATE----->TIMESTAMP(0)
CLOB----->TEXT
TIMESTAMP WITH LOCAL TIME ZONE ------>TIMESTAMPTZ

numer(3,2) 表示总位数3位,其中小数点右边2位,例如 3.14
substr('abcdef',1,4) 从第1位开始,截取4位
cast(xxx as varchar) 

递归查询

--向上查询
with RECURSIVE up_temp AS ( 
       SELECT * FROM  tb WHERE id =1
     union all 
       SELECT a.* 					--a.子=b.父
	   FROM tb a, up_temp b WHERE a.id = b.parentid
	)
 SELECT * FROM up_temp;
 
 
 --向下查询
 with RECURSIVE down_tmp AS ( 
       SELECT * FROM tb WHERE id =2
     union all									--a.父=b.子
       SELECT a.* FROM tb a, down_tmp b WHERE a.parentid = b.id
	)
 SELECT * FROM down_tmp;
 

指定排序(case when)

--  因为PostgreSql没有order by field 可以使用case when then进行排序


SELECT stage, pln_status
FROM project
ORDER BY CASE 
	WHEN pln_status = '规划' THEN 1
	WHEN pln_status = '初选' THEN 2
	WHEN pln_status = '查勘' THEN 3
	WHEN pln_status = '评审' THEN 4
	WHEN pln_status = '批复' THEN 5
	WHEN pln_status = '初设' THEN 6
	WHEN pln_status = '开工' THEN 7
END

PostgreSQL多行变一行

string_agg(字段名，分隔符)

postgres=# select name,string_agg(tag,',' order by id_num asc) from book2 group by name;
 name | string_agg 
------+------------
 C++  | dd,ee
 java | aa,bb,cc
(2 rows)

array_agg(字段名)

select name,array_agg(tag) from tb3 group by name;

 name | array_agg  
------+------------
 c++  | {dd,ee}
 java | {aa,bb,cc}

 
select name,array_to_string(array_agg(tag),',') from tb3 group by name;

 name | array_to_string 
------+-----------------
 c++  | dd,ee
 java | aa,bb,cc

over() partition by order by

-- count(1) OVER(PARTITION BY a ORDER BY a) AS count，是按a进行分组且组内按a进行升序，统计组内记录的条数。
-- row_number() OVER(PARTITION BY a ORDER BY a) AS rownum，是按a进行分组且组内按a进行升序，返回组内行编号。

select name,area,level,
row_number() OVER(PARTITION BY name ORDER BY level) AS rownum
from tb1;

行转列

原始数据

name	address	age
a1	A	12

目标结果

c1	c2
name	a1
address	A
age	12

--concat_ws拼接
select name,address,concat_ws('#',name,address) from tb1;

--行转列(带上列名)
SELECT
	regexp_split_to_table( concat_ws ( ',', 'name', 'address','age' ), ',' ) age_type,
	regexp_split_to_table(concat_ws(',',coalesce("name",'无'),coalesce("address",'无'),coalesce("age",'无')),',') age_rate 
FROM
	tb1;
--结果:	
--	age_type	age_rate
--	name		a1
--	address		A
--	age			12

索引

--创建
create index idx_test01 on tb1;

--组合索引
create index idx_test02 on tb2 (c1,c2);

--唯一索引
create unique idex idx_test03 on tb3 (c1)

--删除索引
drop index