Postgresql 中文操作指南

Chapter 37. The Information Schema

Table of Contents

信息架构包含一套视图，其中包含有关在当前数据库中定义的对象的信息。信息架构在 SQL 标准中定义，因此预计它可以移植并保持稳定——这与系统目录不同，后者特定于 PostgreSQL，并且模仿了实现问题。然而，信息架构视图不包含有关 PostgreSQL 特定功能的信息；若要查询这些功能，您需要查询系统目录或其他 PostgreSQL 特定视图。

The information schema consists of a set of views that contain information about the objects defined in the current database. The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable — unlike the system catalogs, which are specific to PostgreSQL and are modeled after implementation concerns. The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views.

Note

当查询约束信息的数据库时，标准兼容查询有时会返回几行，而不是预期的只返回一行。这是因为 SQL 标准要求约束名称在一个架构中是唯一的，但 PostgreSQL 不会强制执行此限制。PostgreSQL 自动生成的约束名称避免在同一个架构中出现重复，但用户可以指定这样的重复名称。

When querying the database for constraint information, it is possible for a standard-compliant query that expects to return one row to return several. This is because the SQL standard requires constraint names to be unique within a schema, but PostgreSQL does not enforce this restriction. PostgreSQL automatically-generated constraint names avoid duplicates in the same schema, but users can specify such duplicate names.

当查询诸如 ` check_constraint_routine_usage 、 check_constraints 、 domain_constraints ` 和 ` referential_constraints ` 这样的信息架构视图时，可能会出现此问题。其他一些视图也有类似的问题，但包含表名称以帮助区分重复行，例如 ` constraint_column_usage 、 constraint_table_usage 、 table_constraints `。

This problem can appear when querying information schema views such as check_constraint_routine_usage, check_constraints, domain_constraints, and referential_constraints. Some other views have similar issues but contain the table name to help distinguish duplicate rows, e.g., constraint_column_usage, constraint_table_usage, table_constraints.