今天 PostgreSQL bbs 里有人问到如何通过查询数据字典获得表字段信息的问题，虽然比较基础，依然总结下。
首先，在 PostgreSQL 中，提供一种称为元子命令的命令，可以方便的查看数据库对像信息，包括表结构，表索引等信息，如下：
方法一: 通过元子命令查看
通过元子命令 /d 查看表结构，如下：
1 2 3 4 5 6 7 8 9 10 11 12 13
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 基表
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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 模式的视图，如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
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
使用 pg_dump 导出表 skytf.tbl_role 定义，如下：
1
pg_dump -h 127.0 .0 .1 -E UTF8 -t "skytf .tbl_role " -s -v skytf > skytf .tbl_role .ddl
备注：-s 表示仅导出表定义，不导表数据，最后查看文件 skytf.tbl_role 确认下。
参考
附一: 查询表定义的SQL
补充一个查询表结构的ＳＱＬ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
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 列表
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT n.nspname , relname FROM pg_class c , pg_namespace n WHERE c.relnamespace = n.oid AND nspname='schema 名称' AND relkind = 'r' AND relhassubclassORDER BY nspname , relname;
原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/237934.html
