pgsql遇到小问题及小功能记录

一、问题：

1、ERROR: invalid input syntax for integer: ""

使用COALESCE((、a.delete_flag,'0')::int as delete_flag报错，因为delete_flag里面包含空值(''):

改为：COALESCE(NULLIF(pri.delete_flag,''),'0')::int as delete_flag 即可

将空值先替换为null，再转换

二、小功能记录

1、添加字段，修改字段，添加注释

--添加列：

ALTER TABLE 表名 ADD COLUMN  列明   varchar(100) ;

--修改字段长度
ALTER TABLE dw.dim_drug alter COLUMN trade_cn_name type character varying(100);

--添加注释

COMMENT ON COLUMN 表名   IS  '注释';

2、问题 :MYSQL单表5000W，我想一次删除2000W数据。有什么好办法？正常delete会非常慢，另外会造成主备高延时

       1、创建临时表，表结构与原表结构相同，将数据插入临时表，再删除原表，再修改临时表表名为原表。新数据插入的问题；
        2、创建分区，删除分区；
        3、limit删除。IO问题;
        4、假删除，update，修改delete_flag;

3、查看当前正在运行的SQL--查看任务记录详情

SELECT
    procpid,
    START,
    now() - START AS lap,
    current_query
FROM
    (
        SELECT
            backendid,
            pg_stat_get_backend_pid (S.backendid) AS procpid,
            pg_stat_get_backend_activity_start (S.backendid) AS START,
            pg_stat_get_backend_activity (S.backendid) AS current_query
        FROM
            (
                SELECT
                    pg_stat_get_backend_idset () AS backendid
            ) AS S
    ) AS S
WHERE
    current_query <> '<IDLE>'
ORDER BY
    lap ASC;

procpid：进程id 
start：进程开始时间 
lap：经过时间 
current_query：执行中的sql 
怎样停止正在执行的sql 
SELECT pg_cancel_backend(进程id); 
或者用系统函数 
kill -9 进程id;

4、查看当前事务锁等待、持锁信息的SQL

with    
t_wait as   --等待SQL 
(    
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   
),   
t_run as   --正在运行的SQL
(   
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   
),   
t_overlap as   
(   
  select r.* from t_wait w join t_run r on   
  (   
    r.locktype is not distinct from w.locktype and   
    r.database is not distinct from w.database and   
    r.relation is not distinct from w.relation and   
    r.page is not distinct from w.page and   
    r.tuple is not distinct from w.tuple and   
    r.virtualxid is not distinct from w.virtualxid and   
    r.transactionid is not distinct from w.transactionid and   
    r.classid is not distinct from w.classid and   
    r.objid is not distinct from w.objid and   
    r.objsubid is not distinct from w.objsubid and   
    r.pid <> w.pid   
  )    
),    
t_unionall as    
(    
  select r.* from t_overlap r    
  union all    
  select w.* from t_wait w    
)    
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   
string_agg(   
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||   
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||   
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||    
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||    
'SQL (Current SQL in Transaction): '||chr(10)||  
case when query is null then 'NULL' else query::text end,    
chr(10)||'--------'||chr(10)    
order by    
  (  case mode    
    when 'INVALID' then 0   
    when 'AccessShareLock' then 1   
    when 'RowShareLock' then 2   
    when 'RowExclusiveLock' then 3   
    when 'ShareUpdateExclusiveLock' then 4   
    when 'ShareLock' then 5   
    when 'ShareRowExclusiveLock' then 6   
    when 'ExclusiveLock' then 7   
    when 'AccessExclusiveLock' then 8   
    else 0   
  end  ) desc,   
  (case when granted then 0 else 1 end)  
) as lock_conflict  
from t_unionall   
group by   
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

5、查看活跃进程          

SELECT datname,pid,state,query FROM pg_stat_activity where state='active'

6、删除活跃进程          

SELECT pg_cancel_backend(61032)

7、查看锁表

select
 T.PID,
 T.STATE,
 T.QUERY,
 T.WAIT_EVENT_TYPE,
 T.WAIT_EVENT,
 T.QUERY_START
from
 PG_STAT_ACTIVITY T
where
 T.DATNAME ='zyzl_jgpt'
 and T.WAIT_EVENT_TYPE ='Lock';

8、pgsql序列的创建、修改、删除

#创建一个序列
CREATE SEQUENCE xxx_no_seql INCREMENT BY 1 maxvalue 99999999 START 10000000;
#设置序列从20000001开始 修改 两种方法都可
SELECT setval('xxx_no_seql',20000001);
alter sequence xxx_no_seql restart with 20000001
#使用
SELECT nextval('xxx_no_seql');
#查看 r =普通表， i =索引，S =序列，v =视图，m =物化视图， c =复合类型，t = TOAST表，f =外部表
select * from pg_class where relkind='S';
#删除
drop sequence xxx_no_seql;

函数                    描述
currval(regclass)    返回最近一次用 nextval 获取的指定序列的数值
nextval(regclass)    递增序列并返回新值
setval(regclass, bigint)    设置序列的当前数值
setval(regclass, bigint, boolean)    设置序列的当前数值以及 is_called 标志

9.查询索引：

SELECT
	n.nspname AS schemaname,
    c.relname AS tablename,
    i.relname AS indexname,
    t.spcname AS tablespace,
    pg_get_indexdef(i.oid) AS indexdef
FROM pg_index x
    JOIN pg_class c ON c.oid = x.indrelid
    JOIN pg_class i ON i.oid = x.indexrelid
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) 
AND i.relkind = 'i'::"char"

10.替换空格、换行、回车字符

UPDATE table set name = trim(name);//用来删除数据前后的空格 
UPDATE table set name = rtrim(name);//用来删除数据前的空格 
UPDATE table set name = ltrim(name);//用来删除数据后的空格 
UPDATE table set name = REPLACE(name，' ','');//用来删除数据中的空格 
UPDATE table set name =REPLACE(name, CHR(10), '') //替换换行符 
UPDATE table set name =REPLACE(name, CHR(13), '') //替换回车符

11.查询字段名、字段类型及字段长度和字段注释

select a.attnum AS "序号",
c.relname AS "表名",
cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述",
a.attname AS "列名",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "字段类型",
d.description AS "备注"
from pg_class c, pg_attribute a , pg_type t, pg_description d 
where  c.relname = 't_bt_data'
and a.attnum>0 
and a.attrelid = c.oid 
and a.atttypid = t.oid 
and  d.objoid=a.attrelid
and d.objsubid=a.attnum
ORDER BY c.relname DESC,a.attnum ASC ;

12.查询表主键名称；

select distinct  pg_constraint.conname as pk_name,bb.st from
pg_constraint inner join pg_class
on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid
--and pg_attribute.attnum = pg_constraint.conkey::array
inner join pg_type on pg_type.oid = pg_attribute.atttypid
inner join (select tablename,schemaname||'.'||tablename st  from pg_tables where schemaname = 'ods' order by tablename) bb on pg_class.relname = bb.tablename
where pg_constraint.contype='p' 
order by st

1、删除主键

ALTER TABLE "table_name" DROP CONSTRAINT "table_name_pkey";
2. 增加主键约束

ALTER TABLE "table_name" add primary key (xx,xx,xx);
 

13.根据一张表update另一张表数据

单列：

UPDATE TABLE A
    SET ( district ) = ( SELECT district FROM table2 b WHERE A.site = b.site )
WHERE
    bustype = 1;
 

多列：

UPDATE TABLE A
    SET ( column01, colum02, column03 ) = (
SELECT
    b.column01， b.column02,
    b.column03
FROM
    table2 b
WHERE
    A.COLUMN = b.COLUMN)
 

添加行号，删除重复数据：

DELETE 
FROM
    tableA 
WHERE
    ID IN (
SELECT ID 
FROM
    ( SELECT ROW_NUMBER () OVER ( PARTITION BY column01, column02 ), ID FROM tableA ) AS T 
WHERE
    T.ROW_NUMBER > 1 
    );
 

14、需要按照某个字段里面值进行排序

order by case when clume_name like '值' then 1 else 2,id

即可实现将clume_name 列包含‘值’的排列在前面

持续更新中，敬请期待！！！！！！！