pgsql系统表查询-反查表主键索引字段和类型定义

男娘i 2024-04-01 10:52 43阅读 0赞

## pgsql系统表查询-反查表主键索引字段和类型定义 ##

-- 查询schema下有什么表
    SELECT *
    FROM information_schema.tables
    where table_schema = 'my_schema'
    order by table_name;
    
    -- 查询表有什么列（字段信息，含类型等等）
    SELECT *
    FROM information_schema.columns
    where table_schema = 'my_schema'
      and table_name in ('my_table', 'my_table_2')
    order by table_name, ordinal_position;
    
    --查询表有什么索引
    SELECT *
    FROM pg_indexes
    WHERE schemaname = 'my_schema'
      AND tablename = 'my_table';
    
    --查看表有什么索引（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）
    FROM pg_catalog.pg_class cl
         join pg_catalog.pg_namespace na on cl.relnamespace = na.oid
         join pg_constraint co on co.conrelid = cl.oid
    WHERE na.nspname = 'my_schema'
      and cl.relname in ('my_table', 'my_table_2');
    
    --查询表主键索引字段名
    select
        n.nspname
        ,t.schemaname
        ,a.attname
        ,a.attnum
        ,format_type(a.atttypid, a.atttypmod) attr_type
        ,co.contype
    from pg_namespace n
         join pg_tables t on t.schemaname = n.nspname
         join pg_class c on c.relname = t.tablename and c.relnamespace = n.oid
         join pg_attribute a on a.attrelid = c.oid
         left join pg_constraint co on  co.conrelid = c.oid and a.attnum = any(co.conkey) --conkey::text[] 需要使用 any() 查询
    where n.nspname = 'my_schema'
      and t.tablename = 'my_table'
      and a.attnum > 0
      and co.contype = 'p';