注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:如何查询表的字段信息?  

2013-01-05 21:11:01|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


       今天  PostgreSQL bbs  里有人问到如何通过查询数据字典获得表字段信息的问题,虽然比较基础,
依然总结下。

     首先,在 PostgreSQL 中,提供一种称为元子命令的命令,可以方便的查看数据库对像信息,
包括表结构,表索引等信息,如:

方法一:通过 \d 元子命令查看
 skytf=> \d tbl_role
                                  Table "skytf.tbl_role"
  Column   |         Type          |                       Modifiers                       
-----------+-----------------------+-------------------------------------------------------
 id        | integer               | not null default nextval('tbl_role_id_seq'::regclass)
 role_name | character varying(32) | 
 exp       | bigint                | 
 wealth    | bigint                | 
 status    | character(1)          | 
 attr      | hstore                | 
Indexes:
    "tbl_role_pkey" PRIMARY KEY, btree (id)
    "idx_tbl_role_attr" gist (attr), tablespace "tbs_skytf_idx"
   备注:\d 加上表名,就能非常容易的显示表字段信息和索引信息,当然这不是本文开头问题的答案,
             提问的同学是想通过系统数据字典来查看这些信息,方法为以下。
      
       
方法二:查看 catalog 基表
 skytf=> select attrelid ::regclass, attname, atttypid ::regtype, attnotnull, attnum
skytf->   from pg_attribute
skytf->  where attrelid = 'tbl_role' ::regclass
skytf->    and attnum > 0
skytf->    and attisdropped = 'f';
 attrelid |  attname  |     atttypid      | attnotnull | attnum 
----------+-----------+-------------------+------------+--------
 tbl_role | id        | integer           | t          |      1
 tbl_role | role_name | character varying | f          |      2
 tbl_role | exp       | bigint            | f          |      3
 tbl_role | wealth    | bigint            | f          |      4
 tbl_role | status    | character         | f          |      5
 tbl_role | attr      | hstore            | f          |      6
(6 rows)
   备注:系统表 pg_attribute 存储表的每一个列信息,包括系统列,首先通过条件“attnum>0” 排除
             系统列 xmin,ctid 等; 接着通过条件“attisdropped='f'” 排除已被删除的列,因为在 pg 中
             被删除的列并没有物理删除,只是标记,可以通过这个字段过滤。
        
 
方法三:查看 information_schema 模式的视图
 skytf=> select table_schema,
skytf->        table_name,
skytf->        column_name,
skytf->        data_type,
skytf->        column_default,
skytf->        is_nullable
skytf->   from information_schema.columns
skytf->  where table_name = 'tbl_role';
 table_schema | table_name | column_name |     data_type     |            column_default            | is_nullable 
--------------+------------+-------------+-------------------+--------------------------------------+-------------
 skytf        | tbl_role   | id          | integer           | nextval('tbl_role_id_seq'::regclass) | NO
 skytf        | tbl_role   | role_name   | character varying |                                      | YES
 skytf        | tbl_role   | exp         | bigint            |                                      | YES
 skytf        | tbl_role   | wealth      | bigint            |                                      | YES
 skytf        | tbl_role   | status      | character         |                                      | YES
 skytf        | tbl_role   | attr        | USER-DEFINED      |                                      | YES
(6 rows)              
   备注:information_schema.columns 视图存储表和视图的字段信息,与前者不同的是,它并不存储系统
            字段信息,关于这个视图的其它字段,可以参考本文的参考部分。
        
           方法二,方法三是通过查看系统表或视图达到目标的,接下来介绍另一种方法,这种方法能非常全面
   的获得表定义,包括字段,索引,权限,甚至是序列信息,而不仅仅是字段信息。

   
方法四: pg_dump 
--导出表 skytf.tbl_role 定义

 pg_dump -h 127.0.0.1 -E UTF8  -t "skytf.tbl_role" -s -v  skytf > skytf.tbl_role.ddl
  备注: -s 表示仅导出表定义,不导表数据,最后查看文件 skytf.tbl_role 确认下。
  
  
参考:
http://www.postgresql.org/docs/9.2/static/catalog-pg-attribute.html
http://www.postgresql.org/docs/9.2/static/infoschema-columns.html  
http://archives.postgresql.org/pgsql-admin/2008-09/msg00240.php

补充一个查询表结构的SQL
   SELECT
    col.table_schema ,
    col.table_name ,
    col.ordinal_position,
    col.column_name ,
    col.data_type ,
    col.character_maximum_length,
    col.numeric_precision,
    col.numeric_scale,
    col.is_nullable,
    col.column_default ,
    des.description
FROM
    information_schema.columns col LEFT JOIN pg_description des
        ON col.table_name::regclass = des.objoid
    AND col.ordinal_position = des.objsubid
WHERE
    table_schema = 'XXXX'
    AND table_name = 'XXXX'
ORDER BY
    ordinal_position;

  
--查询 table 列表
 SELECT
n.nspname ,
relname
FROM
pg_class c ,
pg_namespace n
WHERE
c.relnamespace = n.oid
AND nspname='schema 名称'
AND relkind = 'r'
AND relhassubclass
ORDER BY
nspname ,
relname;
   
  评论这张
 
阅读(25279)| 评论(5)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016