Postgre 常用sql

1. 获取某表被哪些子表依赖，同时生成添加外键的ddl语句和删除外键的ddl语句，当需要查询的表数量比较少时，可以将以下sql中的图片部分换成tab1,tab2…tabn，如果数据量较多建议新建一张表存储需要查寻的表，或者直接使用外部文件查询的方式也可以

SELECT tp.pk_tab_name, x.table_name, x.column_name,  tp.conname , 
        'alter table '|| x.table_name || ' add constraint ' || tp.conname || ' ' || pg_get_constraintdef(tp.con_id ,true) || ';' as fk_define,
        'alter table '|| x.table_name || ' drop constraint ' || tp.conname || ';' as drop_con
    FROM information_schema.key_column_usage x  
    INNER JOIN 
    (SELECT ft.relname as pk_tab_name, t.relname, a.conname , a."oid" as con_id
    FROM pg_constraint a                                              
    INNER JOIN pg_class ft  ON ft.oid = a.confrelid  
    INNER JOIN pg_class t  ON t.oid = a.conrelid WHERE a.contype = 'f' 
    AND a.confrelid in(
        (select  e.oid   from pg_class e  
        where exists(select 1 from  ：sync_tab_list stl
                        where  e.relname = stl.relname)   
        )     
        )
    ) tp 
    ON (x.table_name = tp.relname AND x.constraint_name = tp.conname)
    where x.table_name not  in(select relname from sync_tab_list)
    order by tp.pk_tab_name ;

2. 查找某表依赖哪些表

SELECT tc.constraint_name,tc.table_name, kcu.column_name,
		ccu.table_name AS foreign_table_name,
		ccu.column_name AS foreign_column_name, 
		tc.is_deferrable,tc.initially_deferred 
  FROM information_schema.table_constraints AS tc 
		JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
		JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
  WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='：tab_name';       

3. 查找那些表包含哪些字段

 select pc.relname , pca.attname 
from pg_class pc
join pg_catalog.pg_attribute pca 
  on pc."oid"  = pca.attrelid 
where exists ( select 1 from sync_tab_list stl where pc.relname = stl.relname )
and pca.attname like '%process%'
order by pc.relname , pca.attnum ;

4. 查找序列被哪个对象使用

select seq_ns.nspname as sequence_schema,seq.relname as sequence_name,tab_ns.nspname as table_schema,tab.relname as related_table
from pg_class seq
  join pg_namespace seq_ns on seq.relnamespace = seq_ns.oid
  JOIN pg_depend d ON d.objid = seq.oid AND d.deptype = 'a' 
  JOIN pg_class tab ON d.objid = seq.oid AND d.refobjid = tab.oid
  JOIN pg_namespace tab_ns on tab.relnamespace = tab_ns.oid
where seq.relkind = 'S' 
  and seq.relname in(select sequence_name from t_seq_sync_list)
  and tab.relname not in(select relname from sync_tab_list)
  and seq_ns.nspname = 'public'
 order by related_table;

5. 逻辑复制常用sql
   SELECT * FROM pg_replication_slots; ##查看事务槽
   select * from pg_publication; ##查看创建发布的结果
   \dRp 查看发布者
   \dRs 查看订阅信息
   select * from pg_publication_tables; ##查看发布关联的表

6、获取表和序列的关系
select ts.nspname as object_schema,
tbl.relname as table_name,
col.attname as column_name,
s.relname as sequence_name
from pg_class s
join pg_namespace sn on sn.oid = s.relnamespace
join pg_depend d on d.refobjid = s.oid and d.refclassid=‘pg_class’::regclass
join pg_attrdef ad on ad.oid = d.objid and d.classid = ‘pg_attrdef’::regclass
join pg_attribute col on col.attrelid = ad.adrelid and col.attnum = ad.adnum
join pg_class tbl on tbl.oid = ad.adrelid
join pg_namespace ts on ts.oid = tbl.relnamespace
where s.relkind = ‘S’
– and s.relname = ‘sequence_name’
and d.deptype in (‘a’, ‘n’)
and tbl.relname = ‘scantist_library_version’;

alter table 表名 alter column 字段 set default nextval(‘sequence_name’);

7、只导出表结构
export PGPASSWORD= & pg_dump -h 172.16.250.103 -U sf -d db_103 -w --verbose -s -t ‘copy_’ -T '_not_exists’ > schema_cve.sql

8、jsonb 格式的数据的行转列
select distinct jsonb_array_elements_text(ss.issue_library)::int as lib_id, ss.id
FROM scantist_scanissue ss
–where id = ‘2098584’

9、批量修改对象的owner
select ‘alter table ’ || ‘"’ || nsp.nspname || ‘"’ || ‘.’ || ‘"’ || cls.relname || ‘"’ || ’ owner to 用户名;’
from pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in(‘public’)
and cls.relnamespace = nsp.oid
and cls.relkind in (‘r’,‘S’,‘v’)
order by nsp.nspname,
cls.relname;

10、备份和恢复
–单个数据库备份（目录/data/pg_dump/dump_dir/需要存在）
pg_dump -h localhost -w --verbose -Fd scantist -j 5 -f /data/pg_dump/dump_dir/
–恢复
pg_restore -h localhost -U scantis -d scantist -w --verbose -O -c --if-exists --verbose -j 10 /data/pg_dump/dump_dir/

11、查看数据库表和索引占用存储空间：
SELECT tmp.rel, (sum(pc.relpages)*8)/1024/1024

–SELECT *
FROM
(SELECT t.relname AS rel, t.indexrelname AS rel2 FROM pg_stat_user_indexes t --WHERE t.relname = ‘scantist_library’
UNION
SELECT t2.relname , t2.relname FROM pg_stat_user_indexes t2 – WHERE t2.relname = ‘scantist_library’
)tmp
JOIN pg_catalog.pg_class pc
ON tmp.rel2 = pc.relname
–WHERE rel = ‘scantist_library’
GROUP BY tmp.rel
ORDER BY 2 DESC;