pg_stat_all_tables、pg_class、pg_tables、pg_indexes、pg_attribute 查看表大小 select pt.schemaname||'.'||pt.tablename,pg_relation_filepath(pt.schemaname
pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = any(pg_constraint.conkey) inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = c.relname and pg_constraint.contype = 'p' a...
pg_stat_all_tables、pg_class、pg_tables、pg_indexes、pg_attribute 查看表大小 selectpt.schemaname||'.'||pt.tablename,pg_relation_filepath(pt.schemaname||'.'||pt.tablename), pg_table_size(pt.schemaname||'.'||pt.tablename), pg_relation_size(pt.schemaname||'.'||pt.tablename), pg_...
--查看表有什么索引(contype索引类型:c = 检查约束;f = 外键约束;p = 主键约束;u = 约束;t = 触发器约束) SELECT na.nspname table_schema, cl.relname table_name, co.conname , co.contype, co.conkey, --主键字段排序码(pg_attribute.attnum) co.confkey --外键字段排序码(pg_attribute.attnum...
pg_namespace.oid = pg_class.relnamespace and pg_attribute.attnum > 0 and pg_class.relkind = 'r' and pg_namespace.nspname = 'public') as a -- 引号更改成自己的数据库名称 left join pg_type b on a.reltype = b.oid left join pg_index c on concat(a.attrelid,a.字段编号) = co...
其中,pg_attribute、pg_class、pg_type等视图可以联合查询来获取表结构信息。不过,这种方法相对复杂,通常不建议直接使用,除非你需要获取更底层的元数据信息。 综上所述,对于大多数用户来说,使用\d命令或查询information_schema.columns系统表是最方便、最直观的方法。以下是一个具体的例子,假设你有一个名为employees...
BEGINSELECTpg_class.oidINTOv_oidFROMpg_class INNERJOINpg_namespaceON(pg_class.relnamespace = pg_namespace.oidANDlower(pg_namespace.nspname) = a_schema_name)WHEREpg_class.relname=a_table_name;IFNOTFOUNDTHENRETURN;ENDIF; v_sql='SELECTpg_attribute.attnameASfields_name, pg_attribute.attnumAS...
9 pg_attribute a 10LEFT OUTER JOIN pg_description b 11ON a.attrelid=b.objoid 12AND a.attnum = b.objsubid,13 pg_type t 14WHERE c.relname ='tablename'15and a.attnum >0 16and a.attrelid = c.oid 17and a.atttypid = t.oid 18ORDER BY a.attnum;--查询prosrc显⽰空...
SELECTa.attnameFROMpg_indexi-- 保存了索引的相关信息JOINpg_attributeaONa.attrelid=i.indrelid-- ...
一、利用表数据信息查询表和字段信息 (一)从pg_tables中查询表信息 select tablename from pg_tables where schemaname='ap' and tablename SIMILAR TO 'dwd_[a-z,_]+_[0-9]+' (二)从pg_class和pg_attribute根据指定的表名查询字段信息 SELECT ...