PostgreSQL 方言数据库的信息架构

使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

信息架构是每个 PostgreSQL 数据库通用的内置架构。您可以对 information_schema 中的表运行 SQL 查询来提取数据库的架构元数据。

例如,以下查询可提取数据库中所有用户定义的表的名称:

  SELECT
    table_name
  FROM
    information_schema.tables
  WHERE
    table_schema = 'public'

用法

  • information_schema 表只能通过 SQL 接口使用,例如:

    • executeQuery API
    • gcloud spanner databases execute-sql 命令
    • Google Cloud 控制台中数据库的查询页面。

    其他单次读取方法不支持 information_schema

PostgreSQL 与 information_schema 的区别

PostgreSQL 方言数据库的 information_schema 表中的列包含开源 PostgreSQL 的 information_schema 中的表,在某些情况下,还包含 Spanner 中的列。在这些表中,开源 PostgreSQL 列排在前面,顺序与开源 PostgreSQL 数据库相同,并且 Spanner 后面会附加所有不同的 Spanner 列。在 Google Cloud CLI 中使用 PostgreSQL 方言数据库时,为 information_schema 的开源 PostgreSQL 版本编写的查询应该无需修改即可运行。

PostgreSQL 方言数据库的 information_schema 中的其他明显差异包括:

  • 开源 PostgreSQL 的部分表列可用,但 PostgreSQL 方言数据库中不会填充这些列。
  • PostgreSQL 方言数据库使用 public 作为架构名称。
  • 自动生成的限制条件名称与开源 PostgreSQL 数据库不同。
  • PostgreSQL 方言数据库不支持的与开源 PostgreSQL 功能相关的表不可用。
  • 一些表在 Spanner 中可用,但在开源 PostgreSQL 中可用,如 database_optionsindex_columnsindexesspanner_statistics

PostgreSQL 方言数据库 info_schema 中的表

information_schema 中的表和视图与开源 PostgreSQL 的 information_schema 中的表和视图兼容。

以下部分介绍了 PostgreSQL 方言数据库的 information_schema 中的表和视图:

check_constraints

check_constraints 视图为由 CHECKNOT NULL 关键字定义的每个检查约束条件分别占一行。

列名 类型 说明
constraint_catalog character varying 数据库名称。
constraint_schema character varying 限制条件的架构名称。PostgreSQL 方言数据库的默认值为“public”。
constraint_name character varying 限制条件的名称。如果未在架构中明确指定限制条件的名称,系统会使用自动生成的名称。
check_clause character varying 检查约束条件表达式。
spanner_state character varying 检查约束的当前状态。可能的状态如下:
  • VALIDATING:PostgreSQL 方言数据库正在验证“ALTER CONSTRAINT”或“ADD CONSTRAINT”命令的现有数据。
  • COMMITTED:此限制条件没有有效的架构更改。

column_column_usage

此视图会列出依赖于同一表中的另一个基本列的所有生成列。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 包含表的架构的名称。默认架构的名称为“public”,其他架构(例如 information_schema 本身)的名称为非空。此列从来不会为 null。
table_name character varying 包含所生成列的表的名称。
column_name character varying 所生成列所依赖的基础列的名称。
dependent_column character varying 所生成列的名称。

column_options

此视图列出了为外键约束引用的表列定义的所有选项。该视图仅包含当前用户有权访问(通过作为所有者或获得权限)的参考表中的列。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 包含外部表的架构的名称。默认架构的名称为“public”,其他架构(例如 information_schema 本身)的名称为非空。此列从来不会为 null。
table_name character varying 外部表的名称。
column_name character varying 列的名称。
option_name character varying 唯一标识选项的 SQL 标识符。此标识符是 DDL 中 OPTIONS 子句的键。
option_value character varying 描述此选项值的 SQL 字面量。此列中的值可作为查询的一部分进行解析。
option_type character varying 表示此选项值的类型的数据类型名称。

columns

此视图提供数据库中所有表列(或视图列)的相关信息。该视图仅包含当前用户有权访问(即以所有者身份或已被授予的权限)的列。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 包含表的架构的名称。默认架构的名称为“public”,其他架构(例如 information_schema 本身)的名称为非空。此列从来不会为 null。
table_name character varying 表的名称
column_name character varying 列的名称
ordinal_position BIGINT 表中列的序号位置,从 1 开始
column_default character varying 列默认值的开源 PostgreSQL 表达式的字符串,例如 '9'::bigint
is_nullable character varying 指明列是否可以为 Null 的字符串。根据 SQL 标准,字符串可以是 YESNO,但不能是布尔值。
data_type character varying 列的数据类型。该值是以下值之一:
  • 对于内置类型,表示数据类型的名称。
  • 对于数组,值为 Array. 如需详细了解数据类型,请参阅视图“element_types”。
character_maximum_length BIGINT 为字符和位字符串数据类型声明的长度上限。如果未指定最大长度,则值为 `NULL`。如果列的数据类型不是字符或位字符串,则值为 `NULL`。
character_octet_length BIGINT 目前未使用。值始终为“NULL”。
numeric_precision BIGINT 当前列的数字数据类型的精度。 对于“双精度”,该值为 53。对于“bigint”,此值为 64。 对于所有其他数据类型,该值为“NULL”。
numeric_precision_radix BIGINT 数值类型的精度基准(单位)。目前仅支持以下两个值:
  • 2 表示“双精度”,“float8”和“bigint”
  • 10 表示“数字”
对于所有其他数据类型,该值为“NULL”。
numeric_scale BIGINT 包含数值列类型的缩放比例,即小数点后的精度基础单位数。对于“bigint”,值为 0。 对于所有其他数据类型,该值为“NULL”。
datetime_precision BIGINT 目前未使用。值始终为“NULL”。
interval_type character varying 目前未使用。值始终为“NULL”。
interval_precision BIGINT 目前未使用。值始终为“NULL”。
character_set_catalog character varying 目前未使用。值始终为“NULL”。
character_set_schema character varying 目前未使用。值始终为“NULL”。
character_set_name character varying 目前未使用。值始终为“NULL”。
collation_catalog character varying 目前未使用。值始终为“NULL”。
collation_schema character varying 目前未使用。值始终为“NULL”。
collation_name character varying 目前未使用。值始终为“NULL”。
domain_catalog character varying 目前未使用。值始终为“NULL”。
domain_schema character varying 目前未使用。值始终为“NULL”。
domain_name character varying 目前未使用。值始终为“NULL”。
udt_catalog character varying 目前未使用。值始终为“NULL”。
udt_schema character varying 目前未使用。值始终为“NULL”。
udt_name character varying 目前未使用。值始终为“NULL”。
scope_catalog character varying 目前未使用。值始终为“NULL”。
scope_schema character varying 目前未使用。值始终为“NULL”。
scope_name character varying 目前未使用。值始终为“NULL”。
maximum_cardinality BIGINT 目前未使用。值始终为“NULL”。
dtd_identifier character varying 目前未使用。值始终为“NULL”。
is_self_referencing character varying 目前未使用。值始终为“NULL”。
is_identity character varying 目前未使用。值始终为“NULL”。
identity_generation character varying 目前未使用。值始终为“NULL”。
identity_start character varying 目前未使用。值始终为“NULL”。
identity_increment character varying 目前未使用。值始终为“NULL”。
identity_maximum character varying 目前未使用。值始终为“NULL”。
identity_minimum character varying 目前未使用。值始终为“NULL”。
identity_cycle character varying 目前未使用。值始终为“NULL”。
is_generated character varying 指明是否生成列的字符串。对于生成的列,字符串为 ALWAYS;对于非生成的列,该字符串为 NEVER
generation_expression character varying 表示所生成列的 SQL 表达式的字符串;如果列不是生成的列,则返回 NULL
is_updatable character varying 目前未使用。值始终为“NULL”。
spanner_type character varying 包含 DDL 兼容类型的字符串的字符串。
is_stored character varying 指明是否存储生成的列的字符串。对于生成的列,字符串始终为 YESNO;对于未生成的列,字符串始终为 NULL
spanner_state character varying 列的当前状态。向现有表添加的新的生成的已存储列可能需要经过多个用户可观察状态才能充分利用。可能的
    值如下:
  • NO_WRITE:不允许对列进行读写操作。存储生成的此状态的列不对客户端造成任何影响。
  • WRITE_ONLY:正在回填列。不允许读取。
  • COMMITTED:该列完全可用。
  • NULL:用于系统架构中的列。

constraint_column_usage

此视图每行显示一项约束所用的每个列。

  • 对于 NOT NULL 关键字定义的 PRIMARY KEYCHECK 约束条件,视图包含这些列。
  • 对于使用 CHECK 关键字创建的 CHECK 约束条件,该视图将包含检查约束表达式使用的列。
  • 对于外键约束,视图包含所引用表的列。
  • 对于 UNIQUE 约束条件,该视图将包含 KEY_COLUMN_USAGE 中的列。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 架构的名称,包含限制条件使用的列所在的表。
table_name character varying 包含限制条件所用列的表的名称。
column_name character varying 限制条件使用的列的名称。
constraint_catalog character varying 数据库名称。
constraint_schema character varying 限制条件的架构的名称。
constraint_name character varying 限制条件的名称。

constraint_table_usage

此视图为约束条件使用的每个表包含一行。对于 FOREIGN KEY 约束条件,该表信息适用于 REFERENCES 子句中的表。对于唯一键或主键约束条件,此视图只能识别该约束条件所属的表。此视图中不包含检查约束条件和非 null 约束条件。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 受限表的架构的名称。
table_name character varying 某个限制条件使用的表的名称。
constraint_catalog character varying 数据库名称。
constraint_schema character varying 包含限制条件的架构的名称。
constraint_name character varying 限制条件的名称。

database_options

此表列出了在数据库上设置的选项。

列名 类型 说明
catalog_name character varying 数据库名称。
schema_name character varying 架构的名称。对于 PostgreSQL 方言数据库,默认值为“public”。
option_name character varying 数据库选项的名称。这是 DDL 中“OPTIONS”子句中“key”的值。
option_type character varying 数据库选项的数据类型。
option_value character varying 数据库选项的值。

index_columns

此表列出了索引中的列。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 包含索引的架构的名称。默认值为“public”。
table_name character varying 与索引关联的表的名称。
index_name character varying 索引的名称。具有“PRIMARY KEY”规范的表具有一个伪索引条目,名为“PRIMARY_KEY”。
index_type character varying 索引的类型。可能的值包括“PRIMARY_KEY”、“LOCAL”或“GLOBAL”。
column_name character varying 列的名称。
ordinal_position BIGINT 索引(或主键)中列的序号位置,从值 1 开始。对于非键列(例如,索引的 INCLUDE 子句中指定的列),此值为 NULL
column_ordering character varying 列的排序顺序。对于键列,值为 ASCDESC;对于非键列(例如,索引的 STORING 子句中指定的列),值为 NULL
is_nullable character varying 指明列是否可以为 Null 的字符串。根据 SQL 标准,字符串可以是 YESNO,而不是布尔值。
spanner_type character varying 存储 DDL 兼容类型的字符串的字符串。

indexes

此视图会列出架构中的索引。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 架构的名称。默认值为“public”。
table_name character varying 表的名称。
index_name character varying 索引的名称。使用 PRIMARY KEY 子句创建的表具有名为 PRIMARY_KEY 的伪索引条目,可用于识别主键的字段。
index_type character varying 索引的类型。值包括 PRIMARY_KEYLOCALGLOBAL
parent_table_name character varying 二级索引可以在父表中交错,如创建二级索引中所述。此列存储该父表的名称,如果索引未交错,则保留 NULL
is_unique character varying 索引键必须是唯一的。根据 SQL 标准,字符串可以是 YESNO,而不是布尔值。
is_null_filtered character varying 索引是否包含值为 NULL 的条目。根据 SQL 标准,字符串可以是 YESNO,而不是布尔值。
index_state character varying 索引的当前状态。可能的值及其表示的状态包括:
  • NULL:索引类型为 `PRIMARY_KEY`
  • PREPARE:为新索引创建空表
  • WRITE_ONLY:回填新索引的数据
  • WRITE_ONLY_CLEANUP:清理新索引
  • WRITE_ONLY_VALIDATE_UNIQUE:检查新索引中数据的唯一性
  • READ_WRITE:正常索引操作
spanner_is_managed character varying 索引是否由 Spanner 管理。例如,外键的辅助后备索引由 Spanner 管理。根据 SQL 标准,字符串可以是 YESNO,而不是布尔值。

information_schema_catalog_name

此表包含一行和一列,其中包含数据库名称。

列名 类型 说明
catalog_name character varying 数据库名称。

key_column_usage

此视图可标识当前数据库中由唯一键、主键或外键约束引用的所有列。如需了解 CHECK 约束列,请参阅 check_constraints 视图。

列名 类型 说明
constraint_catalog character varying 数据库名称。
constraint_schema character varying 限制条件的架构名称。默认值为“public”。
constraint_name character varying 限制条件的名称。
table_catalog character varying 数据库名称。
table_schema character varying 包含包含受限列的表的架构的名称。默认值为“public”。
table_name character varying 包含受此限制条件限制的列的表的名称。
column_name character varying 受限制的列的名称。
ordinal_position BIGINT 列在约束键中的序数位置,从值为 1 开始。
position_in_unique_constraint BIGINT 对于 FOREIGN KEY,唯一约束条件中列的序号位置,从值 1 开始。对于其他限制类型,此列的值为“NULL”。

referential_constraints

此视图包含与每个 FOREIGN KEY 约束条件有关的一行。您只能看到对引用表具有写入权限的限制。此视图还标识了外键用于强制执行约束和引用操作的引用的 PRIMARY KEYUNIQUE 约束条件。

列名 类型 说明
constraint_catalog character varying 数据库名称。
constraint_schema character varying 包含外键约束的架构的名称。默认值为“public”。
constraint_name character varying 外键约束的名称。
unique_constraint_catalog character varying 数据库名称。
unique_constraint_schema character varying 包含外键限制条件引用的唯一或主键限制条件的架构的名称。
unique_constraint_name character varying 外键限制条件引用的唯一或主键限制条件的名称。
match_option character varying 外键约束使用的匹配方法。该值始终为 NONE
update_rule character varying 外键约束的更新规则。此值始终为 NO ACTION
delete_rule character varying 外键约束的删除规则。此值始终为 NO ACTION
spanner_state character varying 外键的当前状态。只有在创建外键的后备索引并回填后,Spanner 才会开始强制执行此限制条件。索引准备就绪后,Spanner 会在验证现有数据时开始对新事务实施限制。可能的值和它们所代表的状态如下:
  • BACKFILLING_INDEXES:正在回填索引。
  • VALIDATING_DATA:现有数据和新写入正在接受验证。
  • WAITING_FOR_COMMIT:外键批量操作已成功完成,或者不需要任何操作,但外键仍处于待处理状态。
  • COMMITTED:已提交架构更改。

schemata

information_schema.schemata 视图为当前数据库中的每个架构占一行。这些架构包括信息架构和名为 public 的默认架构。

列名 类型 说明
catalog_name character varying 数据库名称。
schema_name character varying 架构的名称。默认架构设置为“public”,命名架构设置为“非空”。
schema_owner character varying 架构所有者的姓名。
default_character_set_catalog character varying 目前未使用。
default_character_set_schema character varying 目前未使用。
default_character_set_name character varying 目前未使用。
sql_path character varying 目前未使用。
effective_timestamp timestamp with timezone 此架构中所有数据生效的时间戳。此属性目前仅用于默认架构。

spanner_statistics

下表列出了可用的查询优化器统计信息软件包。

列名 类型 说明
catalog_name character varying 数据库名称。
schema_name character varying 架构的名称。默认架构值为“public”。
package_name character varying 统计信息软件包的名称。
allow_gc character varying 统计信息软件包是否免于被垃圾回收。根据 SQL 标准,字符串可以是 YESNO,而不是布尔值。您必须先将此属性设置为 NO,然后才能在提示中或通过客户端 API 引用统计信息软件包。

table_constraints

此视图包含当前用户有权访问的表(SELECT 除外)的所有限制条件。

列名 类型 说明
constraint_catalog character varying 数据库名称。
constraint_schema character varying 包含限制条件的架构的名称。
constraint_name character varying 限制条件的名称。
table_catalog character varying 数据库名称。
table_schema character varying 包含与限制条件关联的表的架构的名称。
table_name character varying 表的名称。
constraint_type character varying 限制条件的类型。可能的
    值如下:
  • CHECK
  • FOREIGN KEY
  • PRIMARY KEY
  • UNIQUE
is_deferrable character varying 该值始终为 NO
initially_deferred character varying 该值始终为 NO
enforced character varying 是否强制执行限制条件。如果强制执行了限制条件(在达到特定状态后),它会在写入时和后台完整性验证程序进行验证。根据 SQL 标准,字符串可以是 YESNO,而不是布尔值。

tables

此视图包含当前数据库中的所有表和视图。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 包含表或视图的架构的名称。
table_name character varying 表或视图的名称。
table_type character varying 表类型。可能的值包括 &BASE 39; TABLE 和 'VIEW'。
self_referencing_column_name character varying 目前未使用。
reference_generation character varying 目前未使用。
user_defined_type_catalog character varying 目前未使用。
user_defined_type_schema character varying 目前未使用。
user_defined_type_name character varying 目前未使用。
is_insertable_into character varying 目前未使用。
is_typed character varying 目前未使用。
commit_action character varying 目前未使用。
parent_table_name character varying 如果此表是交错表,则为父表的名称,否则为 NULL
on_delete_action character varying 对于交错表,设置为 CASCADENO ACTION,否则设置为 NULL。如需了解详情,请参阅 TABLE 语句
spanner_state character varying 表的当前创建状态。
如果涉及批量操作(例如,在创建表时需要使用需要回填其引用索引的外键),则表可能会在创建期间经历多个状态。可能的状态包括:
  • ADDING_FOREIGN_KEY:添加表的外键
  • WAITING_FOR_COMMIT:完成架构更改
  • COMMITTED:用于创建表的架构更改已提交。在提交更改之前,您无法写入表。
  • NULL:不是基表的表或视图。
interleave_type character varying 此表与交错其中的表之间是否存在父子关系。可能的
    值如下:
  • IN:没有父子关系的“INTERLEAVE IN”表。无论其父表行是否存在,此表中的行都可以存在。
  • IN PARENT:具有父级-子级关系的“INTERLEAVE IN PARENT”表。此表中的行必须存在其父表行。
row_deletion_policy_expression character varying 一个字符串,包含定义 ROW DELETION POLICY 的表达式文本。

views

此视图会列出架构中视图的相关信息。

列名 类型 说明
table_catalog character varying 数据库名称。
table_schema character varying 架构的名称。默认值为“public”。
table_name character varying 视图的名称。
view_definition character varying 定义视图的查询的 SQL 文本。
check_option character varying 目前未使用。
is_updatable character varying 目前未使用。
is_insertable_into character varying 目前未使用。
is_trigger_updatable character varying 目前未使用。
is_trigger_deletable character varying 目前未使用。
is_trigger_insertable_into character varying 目前未使用。

示例

返回默认架构中每个表的相关信息:

SELECT
  t.table_catalog,
  t.table_name,
  t.parent_table_name
FROM
  information_schema.tables AS t
WHERE
  t.table_schema = 'public'
ORDER BY
  t.table_catalog,
  t.table_schema,
  t.table_name

返回 PostgreSQL 方言数据库的 information_schema 中所有表和视图的名称:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = "information_schema"

返回有关用户表 my_table 中的列的信息:

SELECT
  t.ordinal_position,
  t.column_name,
  t.data_type,
  t.spanner_type,
  t.is_nullable
FROM
  information_schema.columns AS t
WHERE
  t.table_schema = 'public'
  AND
  t.table_name = 'my_table'
ORDER BY
  t.ordinal_position

返回当前数据库中默认架构中每个索引的相关信息:

SELECT
  t.table_name,
  t.index_name,
  t.parent_table_name
FROM
  information_schema.indexes AS t
WHERE
  t.table_schema = 'public'
  AND
  t.index_type != 'PRIMARY_KEY'
ORDER BY
  t.table_schema,
  t.table_name,
  t.index_name

返回使用默认值以外的选项的所有列:

SELECT
  t.table_name,
  t.column_name,
  t.option_type,
  t.option_value,
  t.option_name
FROM
  information_schema.column_options AS t
WHERE
  t.table_schema = 'public'
ORDER BY
  t.table_schema,
  t.table_name,
  t.column_name,
  t.option_name

返回与优化器相关的当前数据库选项:

SELECT
  s.option_name,
  s.option_value
FROM
  information_schema.database_options s
WHERE
  s.schema_name=public''
  AND s.option_name IN ('optimizer_version',
    'optimizer_statistics_package')

返回所有可用的统计信息软件包:

SELECT *
FROM information_schema.spanner_statistics;

后续步骤

  • 了解可用的内省工具以帮助您调查数据库问题。