pgsql笔记

--进程相关

--查看进程
SELECT * FROM pg_stat_activity where application_name='Navicat' and query like 'DROP%';
SELECT b.relname, A.pid, A.locktype, A.DATABASE, A.MODE, A.relation
FROM
    pg_locks
    A JOIN pg_class b ON A.relation = b.oid
WHERE
    b.relname IN ( 'yy_dwzz_bf', 'hz_qtzf_yy', 'inc_cert_yy', 'inc_apply_yy', 'pay_plan_voucher_yy', 'bas_agency_info_yy', 'pay_voucher_yy', 'pay_voucher_bill_yy', 'pm_project_info_yy' );
--删除单个进程
SELECT pg_terminate_backend(37541);
--批量删除进程
SELECT pg_terminate_backend(pid) from pg_stat_activity where application_name='Navicat' and query like 'DROP%';

 

--查询所有数据库使用大小

select pg_database.datname, pg_database_size(pg_database.datname)/(1024*1024*1024) AS size from pg_database order by pg_database_size(pg_database.datname) desc;

 

--数字相关函数

--随机数（0-1），默认15位小数
select random();
--四舍五入函数
select round(10.45);
select round(10.45,1);
--取整函数
select ceil(3.36);--向上取整
select floor(3.36);--向下取整
--绝对值
select abs(-3.36);
--取余数
select mod(9,4);
--截断(向零靠近)
select trunc(42.8);
--截断为s小数位置的数字
select trunc(42.438,2);

 

--字符串函数和操作符

--字串连接
select 'Post'||'greSQL';
--字串中的字符个数
select char_length('jose');
select length('900150983cd24fb0d6963f7d28e17f72');
--把字串转化为小写
select lower('TOM');
--把字串转化为大写
select upper('tom');
--替换子字串
select overlay('Txxxxas' placing 'hom' from 2 for 4);
--指定的子字串的位置
select position('om' in 'Thomas');
--抽取子字串
select substring('Thomas' from 2 for 3);
select substring('Thomas', 2,3);
--抽取匹配SQL正则表达式的子字串
select substring('Thomas' from '%#"o_a#"_' for '#');
--计算给出string的MD5散列，以十六进制返回结果
select md5('abc');
--重复string number次
select repeat('Pg', 4);
--把字串string里出现地所有子字串from替换成子字串to
select replace('abcdefabcdef', 'cd', 'XX');
--通过填充字符fill(默认为空白)，把string填充为长度length。如果string已经比length长则将其截断
select rpad('hi', 6, 'xy');
--从字串string的结尾删除只包含character(默认是个空白)的最长的字
select rtrim('trimxxxx','x');
--根据delimiter分隔string返回生成的第field个子字串(1 Base)
select split_part('abc~@~def~@~ghi', '~@~', 2);
--声明的子字串的位置
select strpos('high','ig');
--抽取子字串
select substr('alphabet', 3, 2);
--把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符
select translate('12345', '145', 'axl');

　--左补0函数：lpad()

  select lpad('123', 6, '0'); -- 解释：123不满6位数时，左变补0，让其达到6位长度

  --右补0函数：rpad()

  select rpad('123', 6, '0'); -- 解释：123不满6位数时，右边补0，让其达到6位长度

  --去掉前缀0函数：ltrim()

  select ltrim('0000150','0');
  --去掉后缀0函数：rtrim()

  select rtrim('0000150','0');

 

--类型转换相关函数

--将时间戳转换为字符串,返回text
select to_char(current_timestamp, 'HH12:MI:SS');
--将时间间隔转换为字符串,返回text
select to_char(interval '15h 2m 12s', 'HH24:MI:SS');
--整型转换为字符串
select to_char(125, '999.999');
--双精度转换为字符串
select     to_char(125.8::real, '999D9');
--字符串转换为日期
select to_date('05 Dec 2000', 'DD Mon YYYY');
--转换字符串为数字
select     to_number('12,454.8-', '99G999D9S');
--转换为指定的时间格式 time zone convert string to time stamp
select to_timestamp('05 Dec 2000', 'DD Mon YYYY');

 

--空值替换函数

--COALESCE(col, 'replacement') ：如果col列的值为null，则col的值将被替换为'replacement'
select coalesce(null,'-');

 

--行转列函数

--regexp_split_to_table(col,'splitor')：如果某条记录的col列的值为‘1,2,3,4,5'，而splitor为','，则结果是：该条记录被转换成5条记录，且各条记录的col列的值依次为1、2、3、4、5
select regexp_split_to_table('1,2,3,4,5', ',');

 

--列转行函数

SELECT string_agg(base."column_name",',') FROM information_schema.COLUMNS base WHERE base."table_name" = 'gla_acct_set';

 

--正则

select * from ma_ele_agency where rg_code ~ '330100|330000';

 

--并交差集函数

并集
关键字UNION
例子：query1 UNION query2
 
交集
关键字INTERSECT
例子：query1 INTERSECT query2
 
差集
关键字EXCEPT
例子：query1 EXCEPT query2

 

--可执行代码块

do
$$
declare
v_mof_div_code varchar;
v_agency_code varchar;
v_agency_code_old varchar;
ftable record ;
begin
   FOR ftable IN (select chr_code,ys_code from test_4) LOOP
    v_mof_div_code := left(ftable.chr_code,6);
    v_agency_code := right(ftable.ys_code,6);
    v_agency_code_old := right(ftable.chr_code,6);
    update gla_vou_head set agency_code=v_agency_code where mof_div_code=v_mof_div_code and agency_code=v_agency_code_old;
    update gla_vou_diff set agency_code=v_agency_code where mof_div_code=v_mof_div_code and agency_code=v_agency_code_old;
    update gla_vou_detail set agency_code=v_agency_code where mof_div_code=v_mof_div_code and agency_code=v_agency_code_old;
    update gla_vou_bill_relation set agency_code=v_agency_code where mof_div_code=v_mof_div_code and agency_code=v_agency_code_old;
    update gla_bal set agency_code=v_agency_code where mof_div_code=v_mof_div_code and agency_code=v_agency_code_old;
    update gla_acct_set set agency_code=v_agency_code where mof_div_code=v_mof_div_code and agency_code=v_agency_code_old;
    update gla_account_cls set agency_code=v_agency_code where mof_div_code=v_mof_div_code and agency_code=v_agency_code_old;
    raise notice '%',v_mof_div_code || v_agency_code_old; --控制台输出
END LOOP;
end;
$$;

 

--可执行代码块-数组

do
$$
declare
l_table_name varchar[];
v_table_name varchar;
begin
  l_table_name := array['gla_vou_head','gla_vou_diff','gla_vou_detail_1','gla_vou_detail_2','gla_vou_bill_relation','gla_bal','gla_acct_set','gla_account_cls'];
    FOREACH v_table_name IN ARRAY l_table_name LOOP
        raise notice '%',v_table_name;
    END LOOP;
end;
$$;

 

--数据库清理

大量update或者delete后 磁盘空间会猛增。原理是postgresql并没有真正的删除 只是将删除数据的状态置为已删除，该空间不能记录被从新使用。
若是删除的记录位于表的末端，其所占用的空间将会被物理释放并归还操做系统。若是不是末端数据，该命令会将指定表或索引中被删除数据所占用空间从新置为可用状态，那么在从此有新数据插入时，将优先使用该空间，直到全部被重用的空间用完时，再考虑使用新增的磁盘页面。

vacuum 不会锁表 会释放文件空间 不会释放磁盘空间 效率高 建议常用

vacuum full 会锁表 会释放磁盘空间 效率低 建议大量delete和update后使用。