【postgresql】查看数据中表的信息

切换到postgresql数据库，各种不适应吧。

有个需求需要查询数据表的各种信息。

下面我们一起学习吧。

●PostgreSQL: Documentation

PostgreSQL: Documentation

**●

pg_namespace

**

存储名字空间。名字空间是 SQL 模式下层的结构：每个名字空间有独立的关系， 类型等集合但并不会相互冲突。

OID

在PostgreSQL中，对象标识符Object identifiers (OIDs) 用来在整个数据集簇中唯一的标识一个数据库对象，这个对象可以是数据库、表、索引、视图、元组、类型等等。

OID的分配由系统中的一个全局OID计数器来实现，OID分配时会采用互斥锁加以锁定以避免多个要求分配OID的请求获得相同的OID。

sql

select * from pg_namespace WHERE nspname ='screen';

●

pg_tables

pg_tables

提供了对有关数据库中每个表的有用信息地访问。

SQl

select * from pg_tables  where schemaname = 'creen' and tablename = 'title';

●pg_class

pg_class表记载表和几乎所有有字段或者是那些类似表的东西。 包括索引(不过还要参阅pg_index)、序列、视图、物化视图、 复合类型和一些特殊关系类型；
名字类型引用****描述oidoid行标识符(隐藏属性; 必须明确选择)relnamename表、索引、视图等的名字。relnamespaceoidpg_namespace.oid包含这个关系的名字空间(模式)的 OIDreltypeoidpg_type.oid如果有，则为对应这个表的行类型的数据类型的OID(索引为零，它们没有pg_type记录)。reloftypeoidpg_type.oid对于类型表，为底层复合类型的OID，对于所有其他关系为0relowneroidpg_authid.oid关系所有者relamoidpg_am.oid如果行是索引，那么就是所用的访问模式(B-tree, hash 等等)relfilenodeoid这个关系在磁盘上的文件的名字，0表示这是一个"映射的"关系， 它的文件名取决于行级别的状态reltablespaceoidpg_tablespace.oid这个关系存储所在的表空间。如果为零，则意味着使用该数据库的缺省表空间。 如果关系在磁盘上没有文件，则这个字段没有什么意义。relpagesint4以页(大小为BLCKSZ)的此表在磁盘上的形式的大小。 它只是规划器用的一个近似值，是由VACUUM,ANALYZE 和几个 DDL 命令，比如CREATE INDEX更新。reltuplesfloat4表中行的数目。只是规划器使用的一个估计值，由VACUUM,ANALYZE 和几个 DDL 命令，比如CREATE INDEX更新。relallvisibleint4在表的可见映射中标记所有可见的页的数目。只是规划器使用的一个估计值， 由VACUUM,ANALYZE 和几个 DDL 命令，比如CREATE INDEX更新。reltoastrelidoidpg_class.oid与此表关联的 TOAST 表的 OID ，如果没有为 0 。TOAST 表在一个从属表里"离线"存储大字段。reltoastidxidoidpg_class.oid对于 TOAST 表是它的索引的 OID ，如果不是 TOAST 表则为 0relhasindexbool如果它是一个表而且至少有(或者最近有过)一个索引，则为真。relissharedbool如果该表在整个集群中由所有数据库共享则为真。只有某些系统表 (比如pg_database)是共享的。relpersistencecharp = permanent table（永久表）, u = unlogged table（未加载的表）, t = temporary table （临时表）relkindcharr = ordinary table（普通表）, i = index（索引）, S = sequence（序列）, v = view（视图）, m = materialized view（物化视图）, c = composite type（复合类型）, t = TOAST table（TOAST 表）, f = foreign table（外部表）relnattsint2关系中用户字段数目(除了系统字段以外)。在pg_attribute 里肯定有相同数目对应行。又见pg_attribute.attnum。relchecksint2表里的CHECK约束的数目；参阅pg_constraint表relhasoidsbool如果为关系中每行都生成一个 OID 则为真relhaspkeybool如果这个表有一个(或者曾经有一个)主键，则为真。relhasrulesbool如表有（或曾经有）规则就为真；参阅pg_rewrite表relhastriggersbool如果表有(或者曾经有)触发器，则为真；参阅pg_trigger表relhassubclassbool如果有(或者曾经有)任何继承的子表，为真。relispopulatedbool如果关系是填充的则为真（对所有关系为真，除了一些物化视图）relfrozenxidxid该表中所有在这个之前的事务 ID 已经被一个固定的("frozen")事务 ID 替换。 这用于跟踪该表是否需要为了防止事务 ID 重叠或者允许收缩pg_clog 而进行清理。如果该关系不是表则为零(InvalidTransactionId)。relminmxidxid该表中所有在这个之前的多事务 ID 已经被一个事务 ID 替换。 这用于跟踪该表是否需要为了防止多事务 ID 重叠或者允许收缩pg_clog 而进行清理。如果该关系不是表则为零(InvalidTransactionId)。relaclaclitem[]访问权限。参阅GRANT和REVOKE获取详细信息。reloptionstext[]访问方法特定的选项，使用"keyword=value"格式的字符串

SQl

select * from pg_class where relname = 'title' ;

**● **pg_description

pg_description表可以给每个数据库对象存储一个可选的描述(注释)。 你可以用COMMENT命令操作这些描述，并且可以用psql 的\d命令查看。许多内置的系统对象的描述提供了 pg_description的初始内容。

pg_shdescription 提供了类似的功能，它记录了整个集群范围内共享对象的注释。
名字类型引用****描述objoidoid任意 oid 属性这条描述所描述的对象的 OIDclassoidoidpg_class.oid这个对象出现的系统表的 OIDobjsubidint4对于一个表字段的注释，它是字段号(objoid和classoid 指向表自身)。对于其它对象类型，它是零。descriptiontext作为对该对象的描述的任意文本

SQl

select * from pg_description
where objoid = to_regclass('screen' || '.' || 'title')::REGCLASS::OID;

**● **columns

视图columns包含有关数据库中所有表字段（或者视图字段）的信息。 不包括系统字段（比如oid等）。只有那些当前用户有权访问的字段才会显示出来 （要么是所有者，要么是有些权限）。
名字数据类型****描述table_catalogsql_identifier包含表的数据库的名字（总是当前数据库）table_schemasql_identifier包含表的模式的名字table_namesql_identifier表的名字column_namesql_identifier字段的名字ordinal_positioncardinal_number字段在表中的位置序号（从 1 开始）column_defaultcharacter_data字段的缺省表达式is_nullableyes_or_no如果字段可能为空，则为YES，如果知道它不能为空，则为NO。 非空约束是我们得知字段不能为空的一个手段，但是还可能有其它的。data_typecharacter_data如果它是一个内置类型，那么为字段的数据类型，如果它是某种数组， 则为ARRAY（在这种情况下，参阅视图element_types）， 否则就是USER-DEFINED（这时，类型定义在udt_name和相关的字段上）。 如果字段基于域，这个字段引用底层域类型（而域是在domain_name和相关字段里定义的）。character_maximum_lengthcardinal_number如果data_type标识一个字符或者位串类型，那么就是声明的最大长度； 如果是其它类型或者没有定义最大长度，就是空。character_octet_lengthcardinal_number如果data_type标识一个字符类型，就是以字节记的最大可能长度； 所有其它类型都是空。最大字节长度取决于声明的字节最大长度（见上文）和服务器编码。numeric_precisioncardinal_number如果data_type标识一个数值类型，这个字段包含 （声明的或隐含的）这个字段的数据类型的精度。精度表示有效小数位的长度。 它可以用十进制或者二进制来表示，这一点在numeric_precision_radix 字段里声明。对于其它数据类型，这个字段是空。numeric_precision_radixcardinal_number如果data_type标识一个数值类型，这个字段标识字段 numeric_precision和numeric_scale 里的数据是多少进制的。值要么是 2 要么是 10。对于所有其它数据类型，这个字段是空。numeric_scalecardinal_number如果data_type标识一个精确的数值类型， 那么这个字段包含（声明的或者隐含的）这个字段上这个类型的数值范围。 数值范围表明小数点右边的有效小数位的数目。它可以用十进制（10为基） 或者二进制（二为基）来表示，正如字段numeric_precision_radix 声明的那样。对于所有其它数据类型，这个字段是空。datetime_precisioncardinal_number如果data_type标识一个日期，时间，时间戳，或者间隔类型， 该字段（公开地或隐含地）包含该字段类型的小数秒精度，就是说， 小数位数保持到小数点后面。对于所有其它的数据类型，该字段是null。interval_typecharacter_data如果data_type标识一个间隔类型，这个字段包含这个字段时间间隔声明， 例如，YEAR TO MONTH, DAY TO SECOND等等。 如果没有指定字段限制（也就是，间隔接受所有字段），或对于所有其他数据类型，这个字段是null。interval_precisioncardinal_number用于一个PostgreSQL不可用的特性 （参阅datetime_precision获取间隔类型字段的小数秒精度）character_set_catalogsql_identifier用于PostgreSQL里一个不可用的特性character_set_schemasql_identifier用于PostgreSQL里一个不可用的特性character_set_namesql_identifier用于PostgreSQL里一个不可用的特性collation_catalogsql_identifier包含该字段的排序规则的数据库的名字（总是当前数据库），缺省或者字段的数据类型不可排序时为null。collation_schemasql_identifier包含该字段的排序规则的模式的名字，缺省或者字段的数据类型不可排序时为null。collation_namesql_identifier字段的排序规则的名字，缺省或者字段的数据类型不可排序时为null。domain_catalogsql_identifier如果字段是域类型，就是该域定义所在的数据库的名字（总是当前数据库），否则为null。domain_schemasql_identifier如果字段是域类型，就是域定义所在的模式的名字，否则为null。domain_namesql_identifier如果字段是域类型，就是该域的名字，否则为null。udt_catalogsql_identifier这个字段数据类型（如果适用，就是底层域类型）定义所在的数据库的名字（总是当前数据库）。udt_schemasql_identifier这个字段数据类型（如果适用，就是底层域类型）定义所在的模式名字。udt_namesql_identifier这个字段数据类型（如果适用，就是底层域类型）的名字。scope_catalogsql_identifier用于PostgreSQL里一个不可用的特性scope_schemasql_identifier用于PostgreSQL里一个不可用的特性scope_namesql_identifier用于PostgreSQL里一个不可用的特性maximum_cardinalitycardinal_number总是空，因为在PostgreSQL里数组总是有无限的最大维数dtd_identifiersql_identifier一个该字段的数据类型描述符的标识符，在属于这个表中的所有的数据类型描述符中唯一。 这个字段主要用于和其它这样的标识符实例连接。 （这个标识符的确切格式没有定义并且不保证在将来的版本中保持一样。）is_self_referencingyes_or_no用于PostgreSQL里一个不可用的特性is_identityyes_or_no用于PostgreSQL里一个不可用的特性identity_generationcharacter_data用于PostgreSQL里一个不可用的特性identity_startcharacter_data用于PostgreSQL里一个不可用的特性identity_incrementcharacter_data用于PostgreSQL里一个不可用的特性identity_maximumcharacter_data用于PostgreSQL里一个不可用的特性identity_minimumcharacter_data用于PostgreSQL里一个不可用的特性identity_cycleyes_or_no用于PostgreSQL里一个不可用的特性is_generatedcharacter_data用于PostgreSQL里一个不可用的特性generation_expressioncharacter_data用于PostgreSQL里一个不可用的特性is_updatableyes_or_no如果字段为可更新则为YES，否则为NO （基表中的字段总是可以更新的，而试图中的字段则不一定）
系统目录信息函数
名称返回类型描述format_type(type_oid, typemod)text获得一个数据类型的 SQL 名字pg_get_constraintdef(constraint_oid)text获得一个约束的定义pg_get_constraintdef(constraint_oid, pretty_bool)text获得一个约束的定义pg_get_expr(pg_node_tree, relation_oid)text反编译一个表达式的内部形式，假定其中的任何 Var 指向由第二个参数指示的关系pg_get_expr(pg_node_tree, relation_oid, pretty_bool)text反编译一个表达式的内部形式，假定其中的任何 Var 指向由第二个参数指示的关系pg_get_functiondef(func_oid)text获得一个函数的定义pg_get_function_arguments(func_oid)text获得一个函数定义的参数列表（带有默认值）pg_get_function_identity_arguments(func_oid)text获得标识一个函数的参数列表（不带默认值）pg_get_function_result(func_oid)text获得函数的RETURNS子句pg_get_indexdef(index_oid)text获得索引的CREATE INDEX命令pg_get_indexdef(index_oid, column_no, pretty_bool)text获得索引的CREATE INDEX命令，或者当column_no为非零时只得到一个索引列的定义pg_get_keywords()setof record获得 SQL 关键字的列表及其分类pg_get_ruledef(rule_oid)text获得规则的CREATE RULE命令pg_get_ruledef(rule_oid, pretty_bool)text获得规则的CREATE RULE命令pg_get_serial_sequence(table_name, column_name)text获得一个serial、smallserial或bigserial列使用的序列的名称pg_get_triggerdef(trigger_oid)text获得触发器的CREATE [ CONSTRAINT ] TRIGGER命令pg_get_triggerdef(trigger_oid, pretty_bool)text获得触发器的CREATE [ CONSTRAINT ] TRIGGER命令pg_get_userbyid(role_oid)name获得给定 OID 指定的角色名pg_get_viewdef(view_name)text获得视图或物化视图的底层SELECT命令（已废弃）pg_get_viewdef(view_name, pretty_bool)text获得视图或物化视图的底层SELECT命令（已废弃）pg_get_viewdef(view_oid)text获得视图或物化视图的底层SELECT命令pg_get_viewdef(view_oid, pretty_bool)text获得视图或物化视图的底层SELECT命令pg_get_viewdef(view_oid, wrap_column_int)text获得视图或物化视图的底层SELECT命令；带域的行被包装成指定的列数，并隐含了优质打印pg_options_to_table(reloptions)setof record获得存储选项的名称/值对的集合pg_tablespace_databases(tablespace_oid)setof oid获得在该表空间中有对象的数据库的 OID 的集合pg_tablespace_location(tablespace_oid)text获得这个表空间所在的文件系统的路径pg_typeof(any)regtype获得任意值的数据类型collation for (any)text获得该参数的排序规则to_regclass(rel_name)regclass获得命名关系的OIDto_regproc(func_name)regproc获得命名函数的OIDto_regprocedure(func_name)regprocedure获得命名函数的OIDto_regoper(operator_name)regoper获得命名操作符的OIDto_regoperator(operator_name)regoperator获得命名操作符的OIDto_regtype(type_name)regtype获得命名类型的OIDto_regnamespace(schema_name)regnamespace获得命名模式的OIDto_regrole(role_name)regrole获得命名角色的OID

查询表的注释

SELECT tb.table_name, d.description
FROM information_schema.tables tb
       JOIN pg_class c ON c.relname = tb.table_name
       LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0'
WHERE tb.table_schema = 'screen';

查询列注释

-- 查询列注释
SELECT col.table_catalog,
       col.table_schema   AS tableSchema,
       col.table_name     AS tableName,
       col.column_name    AS fieldName,
       col.is_nullable    AS "isNull",
       col.data_type      AS dataType,
       col.column_default AS defaultValue,
       d.description      AS fieldDescribe
FROM information_schema.columns col
         JOIN pg_class c ON c.relname = col.table_name
         LEFT JOIN pg_description d ON d.objoid = c.oid
    AND d.objsubid = col.ordinal_position
WHERE col.table_schema = 'screen'
ORDER BY col.table_name;