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
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; -- 过滤掉已删除的字段 ...
pg_description 是保存注释信息的系统表,但是比较尴尬的是,只保存了他所在属性列的id pg_class 表记载表和几乎所有有 字段或者是那些类似表的东西。包括索引(不过还要参阅pg_index),序列,视图,复合类型和一些特殊关系类型 pg_attribute 这个表存储了所有属性列 最终结果大致就显示成这样,然后可以把这样的结果放在一个...
一、利用表数据信息查询表和字段信息 (一)从pg_tables中查询表信息 select tablename frompg_tables where schemaname='ap' and tablename SIMILAR TO'dwd_[a-z,_]+_[0-9]+' 1. 2. 3. 4. 5. 6. 7. (二)从pg_class和pg_attribute根据指定的表名查询字段信息 SELECT C.relname, A.attnameASNAME...
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. ...
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_catalog.pg_attribute` 和其他系统表这是更底层的方法,直接查询 PostgreSQL 内部使用的系统表。通常这种方法会复杂一些,但可以提供更多的细节。 ```sql SELECT a.attname AS column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, CASE WHEN a.attnotnull THEN 'NO...
pg_attribute.attname AS fields_name, pg_attribute.attnum AS fields_index, pgsql_type(pg_type.typname::varchar) AS fields_type, pg_attribute.atttypmod-4 as fields_length, CASE WHEN pg_attribute.attnotnull THEN ''not null'' ELSE ''' END...
一、利用表数据信息查询表和字段信息 (一)从pg_tables中查询表信息 select tablename from pg_tables where schemaname='ap' and tablename SIMILAR TO 'dwd_[a-z,_]+_[0-9]+' (二)从pg_class和pg_attribute根据指定的表名查询字段信息 SELECT ...
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' ...