System Catalogs

The PostgreSQL system catalog (pg_catalog) is a schema whose tables contain information about the metadata of objects such as repos, tables, views, and columns in bit.io. The pg_catalog schema is created and managed entirely by the Postgres backend and is read-only. By default, pg_catalog is included in the search path, so it isn't necessary to access its tables with schema-qualified names.

pg_attribute

The pg_attribute table in the pg_catalog contains information about table columns. Some columns of interest in the table include:

Column Name

Description

attrelid

oid of the column's table in pg_class

attname

name of the column

atttypid

data type of the column

atttisdropped

indicates whether the column has been dropped from its table and is no longer valid

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

Example usage

SELECT attname, atttypid FROM pg_attribute WHERE attnotnull = true;

Selects the column name and data type of all columns whose NOT NULL constraint is set to true


pg_class

The pg_class table in the pg_catalog schema contains information about tables and views in bit.io. Some of columns of interest in the table include:

Column Name

Description

relname

name of the table or view

relnamespace

oid of the relation's parent schema in pg_namespace

relkind

'r' = ordinary table, 'v' = view

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

Example usage

SELECT relname  FROM pg_class WHERE relkind = 'v';

Selects the name of all relations with type VIEW.


pg_namespace

The pg_namespace table in the pg_catalog schema contains information about repositories in bit.io. Columns in the table include:

Column Name

Description

oid

row identifier

nspname

name of the repository

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

📘

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

Example usage

SELECT nspname FROM pg_namespace;

Selects the names of all repositories in bit.io.


pg_roles

The pg_roles table in the pg_catalog schema contains information about the database roles in bit.io. Some columns of interest include:

rolname

Role name

rolcreaterole

Role can create more roles

rolcanlogin

Role can login

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

Example usage:

SELECT * FROM pg_roles where rolcanlogin = true

Selects all roles that can login.


pg_tables

The pg_tables table in the pg_catalog schema contains information about tables in bit.io. Some columns of interest in the table include:

Column Name

Description

schemaname

name of the repository the table is in

tablename

name of the table

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

Example usage:

SELECT tablename FROM pg_tables WHERE schemaname = 'MyRepo'

Selects the names of all tables in the repository MyRepo.


pg_views

The pg_views table in the pg_catalog schema contains information about views in bit.io. Some columns of interest in the table include:

Column Name

Description

schemaname

name of the repository the view is in

viewname

name of the view

definition

definition of the view (in the form of a SELECT query)

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

Example usage:

SELECT definition FROM pg_views WHERE schemaname = 'MyRepo' AND viewname = 'MyView'

Selects the definition of a view within the repository MyRepo with name MyView.



Did this page help you?