一、利用表数据信息查询表和字段信息 (一)从pg_tables中查询表信息 select tablename from pg_tables where schemaname='ap' and tablename SIMILAR TO 'dwd_[a-z,_]+_[0-9]+' (二)从pg_class和pg_attribute根据指定的表名查询字段信息 SELECT C.relname, A.attname AS NAME, A.attnotnull AS NOTN...
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...
sql SELECT a.attname AS field_name, t.typname AS field_type FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid WHERE a.attrelid = 'your_table_name'::regclass -- 替换为你的表名 AND a.attnum > 0 -- 过滤掉系统字段 AND NOT a.attisdropped; -- 过滤掉已删除的字段 ...
--查看表有什么索引(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_attribute.attname as colname from pg_constraint inner joinpg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1] inner join pg_type on ...
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 AS A , pg_type as T WHERE C.relname = 'aaa' AND A.attrelid = C.oid AND A.atttypid= T.oid AND A.attnum > 0 AND NOT A.attisdropped 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. ...
SELECTa.attnameFROMpg_indexi-- 保存了索引的相关信息JOINpg_attributeaONa.attrelid=i.indrelid-- ...
and pg_attribute.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_constraint.contype='p' ) pkey on pcolumn.table_name = pkey.relname order by table_catalog, table_schema, ordinal_position提示...
and pg_description.objoid=pg_attribute.attrelid and pg_description.objsubid=pg_attribute.attnum AND pg_attribute.attnum = ANY (pg_index.indkey) )B ON A.column_name = b.attname WHERE A.table_schema = current_schema() and is_nullable='NO' ...