​
Tables

The information_schema.tables view contains all tables, views, sinks, and materialized views defined in the current database.

Materialized views are specific to the information schema of RisingWave. They are not included in the information schema of PostgreSQL.

The information_schema.tables view contains the following columns.

ColumnTypeDescription
table_catalogvarcharName of the current database.
table_schemavarcharName of the schema that contains the table, view, or materialized view. The default schema for user-created objects is public.
table_namevarcharName of the table, view, or materialized view.
table_typevarcharType of the table, view, or materialized view. BASE TABLE for a user-defined table, VIEW for a non-materialized view, MATERIALIZED VIEW for a materialized view, and SYSTEM TABLE for a system table.
is_insertable_intovarcharYES if the table or view is insertable into, NO if not. User-defined tables are always insertable, while views and materialized views are not necessarily.

​
Table constraints

The table_constraints view contains all constraints for tables that the current user owns or has privileges other than SELECT on.

The table_constraints view contains the following columns.

ColumnTypeDescription
constraint_catalogvarcharName of the database that contains the constraint.
constraint_schemavarcharName of the schema that contains the constraint.
constraint_namevarcharName of the constraint.
table_catalogvarcharName of the database that contains the table.
table_schemavarcharName of the schema that contains the table.
table_namevarcharName of the table.
constraint_typevarcharType of the constraint: PRIMARY KEY(p), UNIQUE(u), CHECK(c), or EXCLUDE(x).
is_deferrablevarcharYES if the constraint is deferrable, NO if not.
initially_deferredvarcharYES if the constraint is deferrable and initially deferred, NO if not.
enforcedvarcharYES if the constraint is validated and enforced, NO if not.

TEMPORARY LIMITATION

This view assumes the constraint schema is the same as the table schema, since pg_catalog.pg_constraint only supports primary key.

​
Schemata

The schemata view contains all accessible schemas in the current database for users, either by way of being the owner or having some privilege.

It contains the following columns.

ColumnTypeDescription
catalog_namevarcharName of the database containing the schema.
schema_namevarcharName of the schema.
schema_ownervarcharName of the schema owner.
default_character_set_catalogvarcharName of the database that contains the schema’s default character set.
default_character_set_schemavarcharName of the schema containing the default character set.
default_character_set_namevarcharName of the schema’s default character set.
sql_pathvarcharSQL path specification for the schema.

​
Views

The information_schema.views view contains information about the views in the database.

It contains the following columns.

ColumnTypeDescription
table_catalogvarcharName of the current database.
table_schemavarcharName of the schema that contains the view.
table_namevarcharName of the view.
view_definitionvarcharSQL statement that defines the view.

TEMPORARY LIMITATION

Users with access to information_schema.views can potentially access all views, which poses a security risk. We are working to resolve this limitation. Once the fix is implemented, this message will be removed.

​
Columns

The information_schema.columns view contains information about columns of all tables, views, and materialized views in the database.

ColumnTypeDescription
table_catalogvarcharName of the current database.
table_schemavarcharName of the schema that contains the table, sink, view, or materialized view. The default schema for user-created objects is public.
table_namevarcharName of the table, sink, view, or materialized view.
column_namevarcharName of the column.
ordinal_positionint32Ordinal position of the column within the table (count starts at 1).
is_nullablevarcharYES if the column is possibly nullable; NO if it is known not nullable.
data_typevarcharData type of the column.
is_generatedvarcharALWAYS if the column has a generated value; NEVER if it doesn’t.
generation_expressionvarcharExpression for generating values when is_generated is ALWAYS.
_rw_timestamptimestamptzInternal timestamp indicating when each row was last updated. Only available in the batch query.

​
How to use the information schema views?

You can use various information schema views to determine the makeup of tables, sinks, views, and materialized views in a database.

For example, you can query for names and types of all the tables, views, and materialized views in the current database:

SELECT table_name, table_type
FROM information_schema.tables;

To query for all of the columns in a table, sink, view, or materialized view called taxi_trip:

SELECT column_name
FROM information_schema.columns
WHERE table_name='taxi_trip';

To find out tables, sinks, views, and materialized views that contain a column called trip_id:

SELECT table_name
FROM information_schema.columns
WHERE column_name='trip_id';

Was this page helpful?

OverviewPostgreSQL catalogs