The Information Schema

The information schema (information_schema) is a schema with views that contain information about the metadata of objects such as schemas, tables, columns, and views in bit.io. The data in information_schema conforms to the SQL standard and the schema is always included in any SQL database. By default, information_schema is not in the search path, so tables in the information schema must include a schema qualifier.

We support read-only queries for the following information_schema tables:

information_schema.schemata

The schemata view in the information_schema includes information about the metadata of repositories in bit.io. Relevant information_schema.schemata tables include:

Column Name

Description

schema_name

name of the schema

You can find a full list of columns in the information_schema.schemata table in the official PostgreSQL documentation.

📘

In PostgreSQL, information_schema.schemata contains information about schemas, which map to repositories in bit.io.

Example Usage

SELECT * FROM information_schema.schemata

Retrieves all columns from the schemata table in the information_schema.


information_schema.tables

The tables view in the information_schema includes information about the metadata of tables in bit.io. Relevant information_schema.tables tables include:

Column Name

Description

table_schema

name of the schema that contains the table

table_name

name of the table

table_type

type of the table: BASE TABLE for normal tables or VIEW for views.

You can find a full list of columns in the information_schema.tables table in the official PostgreSQL documentation.

Example Usage

SELECT table_name FROM information_schema.tables WHERE table_schema = 'user_a/MyRepo'

Selects the name of all tables that are in the user_a/MyRepo repository.


information_schema.columns

The columns view in the information_schema includes information about the metadata of columns in bit.io. Relevant information_schema.columns columns include:

Column Name

Description

table_schema

name of the repository the column's table is in

table_name

name of the column's table

column_name

name of the column

column_default

default value for the column

is_nullable

YES if the column can contain NULL values, NO otherwise

data_type

data type of the column

You can find a full list of columns in the information_schema.columns table in the official PostgreSQL documentation.

Example Usage

SELECT column_name,  data_type, column_default FROM information_schema.columns WHERE table_name = 'MyTable' AND table_schema = 'user_a/MyRepo'

Selects the name, data type, and default value of all columns in the table MyTable in the repository user_a/MyRepo.


information_schema.views

The columns view in the information_schema includes information about the metadata of views in bit.io. Relevant information_schema.views columns include:

Column Name

Description

table_schema

name of the repository the view is in

table_name

name of the view

definition

query that defines the view (a SELECT or VALUES statement)

You can find a full list of columns in the information_schema.views table in the official PostgreSQL documentation.

Example Usage

SELECT table_name, definition FROM information_schema.views WHERE table_schema = 'user_a/MyRepo'

Selects the name and query definition of all views in the repository user_a/MyRepo.



Did this page help you?