Common SQL¶

1. DB maintain¶

1.1 DB maintain¶

1. Export/Import¶

Table export/import

1.2 Table maintain¶

truncate 单个表

truncate table fa131.table1 cascade;

truncate schema下所有表

查询schema下所有表，并生成 truncate SQL

select 'truncate table '|| string_agg(schemaname || '.' || relname,' ,')||';' from pg_stat_user_tables where schemaname='fa131';

复制并执行生成的如下 truncate SQL，快速清理schema下所有表数据，但会保留表架构

truncate table fa131.table1;
truncate table fa131.table2;
-- clear all the tables one by one

drop 外部表

查询schema下所有外部表，并生成drop SQL

-- PostgreSQL 13
SELECT 'drop foreign table ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' cascade;'
FROM pg_foreign_table ft
JOIN pg_class c ON ft.ftrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'fa131';

-- PostgreSQL 16
SELECT 'drop foreign table ' || schemaname || '.' || tablename || ' cascade;'
FROM pg_foreign_table
JOIN information_schema.tables 
  ON pg_foreign_table.ftrelid = ('"' || tables.table_schema || '"."' || tables.table_name || '"')::regclass
WHERE table_schema = 'fa131';

复制并执行生成的如下 drop SQL，批量drop schema下所有外部表

drop foreign table fa131.table1 CASCADE;
drop foreign table fa131.table2 CASCADE;

1.3 Schema maintain¶

2. Others¶

1. 常用查询¶

查询DB下所有表大小（按从大到小排序）

SELECT 
    schemaname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(relid) DESC;