Information schema for PostgreSQL-dialect databases

The information schema is a built-in schema that's common to every PostgreSQL database. You can run SQL queries against tables in the information_schema to fetch schema metadata for a database.

For example, the following query fetches the names of all user-defined tables in a database:

 SELECT  table_schema,  table_name  FROM  information_schema.tables  WHERE  table_schema NOT IN ('pg_catalog', 'information_schema', 'SPANNER_SYS')  AND table_type = 'BASE TABLE' 

Usage

• information_schema tables are available only through SQL interfaces, for example:

  • The executeQuery API
  • The gcloud spanner databases execute-sql command
  • The Query page of a database in the Google Cloud console.

  Other single read methods don't support information_schema.

• Queries against the information_schema can use strong, bounded staleness, or exact staleness timestamp bounds.
• If you are using a GoogleSQL-dialect database, see Information schema for GoogleSQL-dialect databases.

Differences from information_schema for PostgreSQL

The tables in the information_schema for PostgreSQL-dialect databases include columns from the tables in the information_schema for open source PostgreSQL and in some cases also include columns from Spanner. In these tables, the open source PostgreSQL columns come first and in the same order as they do for a open source PostgreSQL database, and any distinct columns for Spanner are appended afterwards. Queries written for the open source PostgreSQL version of information_schema should work without modification when using PostgreSQL-dialect databases in Google Cloud CLI.

Other notable differences in the information_schema for PostgreSQL-dialect databases are:

• Some of the table columns for open source PostgreSQL are available, but not populated in PostgreSQL-dialect databases.
• PostgreSQL-dialect databases use public for the default schema name.
• Automatically generated constraint names use a different format than open source PostgreSQL databases.
• Tables related to open source PostgreSQL features that are not supported in PostgreSQL-dialect databases are not available.
• Some tables that are available with Spanner but not open source PostgreSQL, such as database_options, index_columns, indexes, and spanner_statistics are available.

Row filtering in information_schema tables and views

Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role (or to members of that role) can see all rows in all information_schema tables and views. For other principals, Spanner filters rows based on the current database role. The table and view descriptions in the following sections indicate how Spanner filters rows for each table and view.

Tables in information_schema for PostgreSQL-dialect databases

The tables and views in the information_schema are compatible with the tables and views in the information_schema of open source PostgreSQL.

The following sections describe the tables and views in the information_schema for PostgreSQL-dialect databases.

applicable_roles

This row-filtered view lists all role memberships that are explicitly granted to all database roles. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only the role memberships that are granted to the current database role or to a role of which the current database role is a member.

Because all database roles are members of the public role, the results omit records for implicit membership in the public role.

change_stream_columns

This row-filtered view contains information about table columns and the change streams that watch them. Each row describes one change stream and one column. If a change stream tracks an entire table, then the columns in that table don't show in this view.

Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only rows for change streams on which the SELECT privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

change_stream_options

This row-filtered view contains the configuration options for change streams. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only options for change streams on which the SELECT privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

change_stream_privileges

This row-filtered view lists all fine-grained access control privileges granted on all change streams to any database role, including public. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change streams to the current database role, to roles of which the current database role is a member, or to public.

change_stream_tables

This row-filtered view contains information about tables and the change streams that watch them. Each row describes one table and one change stream. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only rows for change streams on which the SELECT privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

The data in change_stream_tables does not include the implicit relationships between tables and change streams that track the entire database.

change_streams

This row-filtered view lists all of a database's change streams, and notes which ones track the entire database versus specific tables or columns. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only change streams on which the SELECT fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

check_constraints

The check_constraints view contains one row for each check constraint defined by either the CHECK or the NOT NULL keyword.

column_column_usage

This view lists all the generated columns that depend on another base column in the same table.

column_options

This view lists all the options defined for the referenced table columns of a foreign key constraint. The view contains only those columns in the reference table that the current user has access to (by way of being the owner or granted privileges).

column_privileges

This row-filtered view lists all fine-grained access control privileges granted on all columns to any database role, including public. Principals that have IAM database-level permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on columns to the current database role, to roles of which the current database role is a member, or to public.

The view includes the SELECT, INSERT, and UPDATE privileges that the column inherits from the table or view that contains the column.

columns

This row-filtered view provides information about all table columns and view columns in the database. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only columns that have any fine-grained access control privileges granted on them (or the SELECT, INSERT or UPDATE privileges granted on their containing tables) to the current database role, to roles of which the current database role is a member, or to public.

constraint_column_usage

This view contains one row about each column used by a constraint.

• For PRIMARY KEY and CHECK constraints defined by the NOT NULL keyword, the view contains those columns.
• For CHECK constraints created with the CHECK keyword, the view includes the columns used by the check constraint expression.
• For foreign key constraints, the view contains the columns of the referenced table.
• For UNIQUE constraints, the view contains the columns from KEY_COLUMN_USAGE.

constraint_table_usage

This view contains one row for each table used by a constraint. For FOREIGN KEY constraints, the table information is for the tables in the REFERENCES clause. For a unique or primary key constraint, this view identifies the table the constraint belongs to. Check constraints and not-null constraints are not included in this view.

database_options

This table lists the options that are set on the database.

enabled_roles

This row-filtered view lists the defined database roles. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all database roles. All other principals can see only database roles to which they have been granted access either directly or through inheritance. All system roles excluding public also appear in this view.

index_columns

This view lists the columns in an index.

indexes

This view lists the indexes in a schema.

information_schema_catalog_name

This table contains one row and one column containing the database name.

key_column_usage

This view identifies all columns in the current database that are referenced by a unique, primary key, or foreign key constraint. For information about CHECK constraint columns, see the check_constraints view.

parameters

This row-filtered view defines the arguments for each change stream read function. Each row describes one argument for one change stream read function.

Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only parameters for change stream read functions on which the EXECUTE fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

placements

This table lists the placements in the database.

placement-options

For each placement, this table lists the options that are set on the placement in the OPTIONS clause of the CREATE PLACEMENT statement.

locality-group-options

For each locality group, this table lists the name and options that are set on the locality group in the OPTIONS clause of the CREATE LOCALITY GROUP statement.

referential_constraints

This view contains one row about each FOREIGN KEY constraint. You can see only those constraints for which you have write access to the referencing table. This view also identifies the PRIMARY KEY and UNIQUE constraints on the referenced tables that the foreign keys use for constraint enforcement and referential actions.

role_change_stream_grants

This row-filtered view lists the SELECT privileges granted on all change streams to any database role, including public. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change streams to the current database role and to roles of which the current database role is a member, not including public.

role_column_grants

This row-filtered view lists all fine-grained access control privileges granted on all columns to any database role, including public. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on columns to the current database role and to roles of which the current database role is a member, not including public.

The view includes the SELECT, INSERT, and UPDATE privileges that the column inherits from the table or view that contains the column.

role_routine_grants

This row-filtered view lists the EXECUTE privileges granted on all change stream read functions to any database role, including public. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change stream read functions to the current database role and to roles of which the current database role is a member, not including public.

role_table_grants

This row-filtered view lists all fine-grained access control privileges granted on all tables and views to any database role, including public. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on tables and views to the current database role and to roles of which the current database role is a member, not including public.

routine_options

This row-filtered view contains one row for each option for each defined change stream read function.

Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only options for change stream read functions on which the EXECUTE fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

routine_privileges

This row-filtered view lists all fine-grained access control privileges granted on all change stream read functions to any database role, including public. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change stream read functions to the current database role, to roles of which the current database role is a member, or to public.

routines

This row-filtered view lists all of a database's change stream read functions. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only change stream read functions on which the EXECUTE fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

schemata

The information_schema.schemata view contains one row for each schema in the current database. The schemas include the information schema and a default schema named public.

sequences

The information_schema.sequences view contains the sequences metadata.

spanner_statistics

This table lists the available query optimizer statistics packages.

table_constraints

This view contains all constraints belonging to tables that the current user has access to (other than SELECT).

table_privileges

This row-filtered view lists all fine-grained access control privileges granted on all tables and views to any database role, including public. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on tables and views to the current database role, to roles of which the current database role is a member, or to public.

tables

This row-filtered view lists all the tables and view that are in the current database. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all tables and views. All other principals can see only tables that meet either of the following requirements:

• The SELECT, INSERT, UPDATE, or DELETE fine-grained access control privileges are granted on the table to the current database role, to roles of which the current database role is a member, or to public.
• The SELECT, INSERT, or UPDATE privileges are granted on any table column to the current database role, to roles of which the current database role is a member, or to public.

table_synonyms

This table lists synonym information for the table.

views

This row-filtered view lists all views in the current database. Principals that have database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all views. All other principals can see only views that have the SELECT fine-grained access control privilege granted on them to the current database role, to roles of which the current database role is a member, or to public.

Examples

Return information about each table in the user's schema:

SELECT  t.table_schema,  t.table_catalog,  t.table_name,  t.parent_table_name FROM  information_schema.tables AS t WHERE  t.table_schema NOT IN ('pg_catalog', 'information_schema', 'SPANNER_SYS')  AND t.table_type = 'BASE TABLE' ORDER BY  t.table_catalog,  t.table_schema,  t.table_name 

Return the name of all tables and views in the information_schema for PostgreSQL-dialect databases:

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

Return information about columns in the user table my_table in the default schema:

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 

Return information about each index in the default schema in the current database: ```postgresql 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

Return columns that use non-default options in the default schema:

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 

Return the current optimizer-related database options:

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') 

Return all available statistics packages:

SELECT * FROM information_schema.spanner_statistics; 

What's next

• Learn about available Introspection tools to help you investigate database issues.