原 PG或GreenPlum获取某个表上索引和约束的DDL创建语句
Tags： 原创PGGreenPlumPostgreSQL索引DDL约束DDL语句创建语句获取
👉 本文共约188个字，系统预计阅读时间或需1分钟。
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'; |
工具类
1、\d 表名
1 2 3 4 5 6 7 8 9 10 11 12 13 | db1=# \d bb Table "public.bb" Column | Type | Modifiers --------+-----------------------+----------- id | character varying(64) | not null name | character varying(64) | not null name1 | character varying(64) | not null name2 | character varying(64) | Indexes: "outp_cliug_pkddey" UNIQUE, btree (id, name, name1) "idx_ocod_clrdd_id_bak" btree (id) "idxzddcf" btree (id, name) Distributed by: (id) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
