pg_get_indexdef(index_oid) 描述:获取索引的CREATE INDEX命令。 返回类型:text index_oid为索引的OID,可以通过PG_STATIO_ALL_INDEXES系统视图查询。 示例:查询索引ds_ship_mode_t1_index1的OID及其创建命令。 SELECT indexrelid FROM PG_STATIO_ALL_INDEXES WHERE indexrelname = 'ds_ship_mode_t1_index1'; ...
pg_get_indexdef(indexrelid) AS index_def, indexrelid::regclass index_name , indrelid::regclass table_name, array_agg(attname order by attnum) AS index_att FROM pg_index i JOIN pg_attribute a ON i.indexrelid = a.attrelid GROUP BY pg_get_indexdef(indexrelid) , indrelid, indexrelid ...
pg_get_indexdef(ix.indexrelid)是索引的定义,包括索引类型和涉及的列。 注意事项 确定要查询索引的表名:在替换your_table_name时,确保表名是正确的,并且该表存在于你的数据库中。 执行SQL语句:在PostgreSQL客户端(如psql)或任何支持SQL执行的PostgreSQL工具中执行上述SQL语句。 查看并解读执行结果:执行结果将显示...
,c.relname as "index" ,pg_get_indexdef(indexrelid) as "def" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid JOIN pg_catalog.pg_class t ON i.indrelid = t.oid WHERE c.relkind = 'i'...
select pg_get_indexdef(b.indexrelid); 查看过程函数定义 select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610 select * from pg_get_functiondef(24610); 查看表大小(不含索引等信息) select pg_relation_size('cc'); --368640 byte ...
select pg_get_indexdef(b.indexrelid); --查看过程函数定义 select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610 select * from pg_get_functiondef(24610); --查看表大小(不含索引等信息) select pg_relation_size('cc'); --368640 byte ...
attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t....
PG常用脚本 PG常⽤脚本 作者 digoal ⽇期 2020-05-09 标签 PostgreSQL , DBA 背景 建⽴视图, ⽅便查询 create schema dba;create view dba.invalid_index as select indisvalid, indexrelid::regclass, indrelid::regclass, pg_get_indexdef(indexrelid) from pg_index where not indisvalid;create view...
get_immv_def(immv regclass)RETURNStext IMMV 元数据视图 pg_ivm_immv存贮 IMMV 信息。 六、更多例子 通常,IMMVs比REFRESH MATERIALIZED VIEW更新的更快,但代价是更新基表会更慢,原因是IVVM为了更新物化视图在基表上使用了触发器。 假设定义了一个普通的物化视图: ...
createschema dba;createviewdba.invalid_indexasselectindisvalid, indexrelid::regclass, indrelid::regclass, pg_get_indexdef(indexrelid)frompg_indexwherenotindisvalid;createviewdba.ro_conflictsasselectdatname,pg_stat_get_db_conflict_all(oid) conflict_all,pg_stat_get_db_conflict_bufferpin(oid) confl...