原 PG或GreenPlum获取某个表上索引和约束的DDL创建语句
Tags: 原创PGGreenPlumPostgreSQL索引DDL约束DDL语句获取创建语句
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- 约束(先创建约束再创建索引,会避免主键索引重复问题) SELECT conrelid::regclass tbname, conname, 'alter table '||conrelid::regclass||' add CONSTRAINT '|| conname ||' '||pg_get_constraintdef(oid)||' ;' AS condef FROM pg_constraint WHERE conrelid = 'public.bb'::regclass; -- 表和索引 select n.nspname AS schemaname, a.relname, a.reltuples::numeric as rowcount, -- pg_get_table_distributedby(a.oid) distributedby, -- case a.relstorage when 'a' then ' append-optimized' when 'c' then 'column-oriented' when 'h' then 'heap' when 'v' then 'virtual' when 'x' then 'external table' end as "data storage mode", pg_size_pretty(pg_table_size ( '"' || nspname || '"."' || relname || '"' )) AS table_size, pg_size_pretty(pg_indexes_size ( '"' || nspname || '"."' || relname || '"' )) AS all_indexes_size, pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS all_total_size, b.indexname, b.indexdef, pg_size_pretty(pg_relation_size('"'||b.indexname||'"')) current_index_size from pg_class a LEFT JOIN pg_namespace N ON ( N.oid = a.relnamespace ) left join pg_indexes b on a.relname=b.tablename and n.nspname=b.schemaname where a.relname='t_hash' and schemaname='public'; |
GreenPlum
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- 约束(先创建约束再创建索引,会避免主键索引重复问题) SELECT conrelid::regclass tbname, conname, 'alter table '||conrelid::regclass||' add CONSTRAINT '|| conname ||' '||pg_get_constraintdef(oid)||' ;' AS condef FROM pg_constraint WHERE conrelid = 'public.bb'::regclass; -- 表和索引 select n.nspname AS schemaname, a.relname, a.reltuples::numeric as rowcount, pg_get_table_distributedby(a.oid) distributedby, case a.relstorage when 'a' then ' append-optimized' when 'c' then 'column-oriented' when 'h' then 'heap' when 'v' then 'virtual' when 'x' then 'external table' end as "data storage mode", pg_size_pretty(pg_table_size ( '"' || nspname || '"."' || relname || '"' )) AS table_size, pg_size_pretty(pg_indexes_size ( '"' || nspname || '"."' || relname || '"' )) AS all_indexes_size, pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS all_total_size, b.indexname, b.indexdef, pg_size_pretty(pg_relation_size('"'||b.indexname||'"')) current_index_size from pg_class a LEFT JOIN pg_namespace N ON ( N.oid = a.relnamespace ) left join pg_indexes b on a.relname=b.tablename and n.nspname=b.schemaname where a.relname='t_hash' and schemaname='public'; |