Author Avatar

admin

July 15, 2021
0

Share post:

PostgreSQL database provides two kinds of repositories or set of tables/views etc which you can query to get the information about the databases, transactions, tables, views, triggers, functions, all types of objects, and many other aspects of the database.

Method 1 – Using tables/views in pg_catalog schema (native to PostgreSQL). The full list is here.

Method 2 – Using tables/views in information_schema schema (built on top of pg_catalog schema to present the metadata in a schema according to SQL standards)

In general, all views and tables in pg_catalog and information_schema will have information about currently connected database only. But, some views in pg_catalog schema may have cluster-wide information. For example, view pf_catalog.pg_database stores information of all databases in the current cluster but there is no such corresponding view in information_schema. Similarly, view pg_catalog.pg_roles stores information of all users in the PostgreSQL cluster.

In PostgreSQL, objects which can store or represent some data are called relations. So, table, view, materialized view, sequence, foreign table, partitioned table are treated as relations.

Let’s have a look at some important views.

Listing all databases in the cluster and their properties

  • pg_catalog.pg_database

Listing information of current database

  • \c
  • information_schema.information_schema_catalog_name ()
  • SELECT current_database();
  • SELECT * FROM current_catalog;
  • SELECT current_catalog;

Listing database authorization identifiers in the cluster (essentially a list of inbuilt and user-created users)

  • pg_catalog.pg_authid
  • pg_catalog.pg_roles (pg_roles is a publicly readable view on pg_authid that blanks out the password field)

Listing all users in the cluster (showing only user-created users and postgres user)

  • \du
  • pg_catalog.pg_roles (column rolname !~ ‘^pg_’)
  • pg_catalog.pg_user

Listing information of the current user

  • \c
  • select * from current_role;
  • select current_user;
  • select session_user;
  • select * from session_user;
  • select user;
  • select * from user;

Listing all the membership relations between roles in the cluster (roles granted to other roles)

  • \dg
  • pg_catalog.pg_auth_members (its corresponding older version view pg_group is now deprecated)

Listing all schemas in a database

  • \dn
  • information_schema.schemata
  • pg_catalog.pg_namespace

Listing information of the current schema

  • select * from current_schema;
  • select current_schema;
  • select current_schemas(True);
  • select * from current_schemas(True);

Listing information of all objects in the current database

  • \d (shows everything in all schemas including DDL of all objects)
  • \d public.* (shows everything in schema public including DDL of all objects)
  • \d (public|myapp).* (shows everything in schemas public and myapp including DDL of all objects)
  • \dtpvmsi – tables/partitioned tables/views/materialized views/sequences
  • \dS – tables/partitioned tables/views/materialized views/sequences and associated indexes, constraints, rules, and triggers
  • \dT – types
  • \df – funtions/procedures
  • pg_catalog.pg_class (column relkind -> WHEN ‘r’ THEN ‘table’ WHEN ‘v’ THEN ‘view’ WHEN ‘m’ THEN ‘materialized view’ WHEN ‘S’ THEN ‘sequence’ WHEN ‘f’ THEN ‘foreign table’ WHEN ‘p’ THEN ‘partitioned table’ WHEN ‘i’ THEN ‘index’)

Listing tables and views

  • \dt and \dv
  • information_schema.tables – list all tables and views in all schemas including in the schemas information_schema and pg_catalog
  • information_schema.views – list all views in all schemas including in the schemas information_schema itself and pg_catalog
  • pg_catalog.pg_tables – list all tables in all schemas including in the schemas information_schema itself and pg_catalog
  • pg_catalog.pg_views – list all views in all schemas including in the schemas information_schema itself and pg_catalog
  • pg_catalog.pg_partitioned_table – list all table partitions
  • pg_catalog.pg_matviews – list all materialized views

Listing indexes

  • \di
  • information_schema.indexes
  • pg_catalog.pg_index

Listing functions and procedures

  • \df
  • information_schema.routines
  • pg_catalog.pg_proc (column – prokind ‘a’ -> ‘agg’, ‘w’ -> ‘window’, ‘p’ -> ‘proc’, ‘f’ -> ‘func’)

Listing sequences

  • \ds
  • information_schema.sequences
  • pg_catalog.pg_class (column relkind = ‘S’)

Listing triggers

  • \d+ <table_name>
  • information_schema.triggers
  • pg_catalog.pg_trigger

Listing types

  • \dT
  • information_schema.user_defined_types
  • pg_catalog.pg_type (use with pg_enum)

Listing statistics

  • \dT
  • information_schema.statistics
  • pg_catalog.pg_statistic
  • pg_catalog.pg_statistic_ext
  • pg_catalog.pg_statistic_ext_data

Listing privileges

  • \dp
  • information_schema.table_privileges
  • pg_catalog.pg_class (column relacl)
  • pg_catalog.pg_policy (row level security)
  • pg_catalog.pg_policy (row level security)

Listing columns of the relations (tables, views, etc)

  • information_schema.columns
  • pg_catalog.pg_attribute

Listing constraints

  • information_schema.table_constraints
  • information_schema.domain_constraints (A domain is essentially a data type with optional constraints (restrictions on the allowed set of values))
  • pg_catalog.pg_constraint

Listing tablespaces

  • pg_catalog.pg_tablespace

Listing user mapping

  • \deu+
  • pg_catalog.pg_user_mappings

PostgreSQL supports the SQL standard of storing all the metadata information of the database in the schema information_schema. This schema automatically exists in all databases. The full details can be found here.

Below SQL will give details of all the tables/views which have metadata information about the currently connected database.

# SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables
WHERE table_schema = 'information_schema' ORDER BY 1, 2, 3, 4;

ppdb=# SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables
WHERE table_schema = 'information_schema' ORDER BY 1, 2, 3, 4;
 table_catalog |    table_schema    |              table_name               | table_type 
---------------+--------------------+---------------------------------------+------------
 appdb         | information_schema | _pg_foreign_data_wrappers             | VIEW
 appdb         | information_schema | _pg_foreign_servers                   | VIEW
 appdb         | information_schema | _pg_foreign_table_columns             | VIEW
 appdb         | information_schema | _pg_foreign_tables                    | VIEW
 appdb         | information_schema | _pg_user_mappings                     | VIEW
 appdb         | information_schema | administrable_role_authorizations     | VIEW
 appdb         | information_schema | applicable_roles                      | VIEW
 appdb         | information_schema | attributes                            | VIEW
 appdb         | information_schema | character_sets                        | VIEW
 appdb         | information_schema | check_constraint_routine_usage        | VIEW
 appdb         | information_schema | check_constraints                     | VIEW
 appdb         | information_schema | collation_character_set_applicability | VIEW
 appdb         | information_schema | collations                            | VIEW
 appdb         | information_schema | column_column_usage                   | VIEW
 appdb         | information_schema | column_domain_usage                   | VIEW
 appdb         | information_schema | column_options                        | VIEW
 appdb         | information_schema | column_privileges                     | VIEW
 appdb         | information_schema | column_udt_usage                      | VIEW
 appdb         | information_schema | columns                               | VIEW
 appdb         | information_schema | constraint_column_usage               | VIEW
 appdb         | information_schema | constraint_table_usage                | VIEW
 appdb         | information_schema | data_type_privileges                  | VIEW
 appdb         | information_schema | domain_constraints                    | VIEW
 appdb         | information_schema | domain_udt_usage                      | VIEW
 appdb         | information_schema | domains                               | VIEW
 appdb         | information_schema | element_types                         | VIEW
 appdb         | information_schema | enabled_roles                         | VIEW
 appdb         | information_schema | foreign_data_wrapper_options          | VIEW
 appdb         | information_schema | foreign_data_wrappers                 | VIEW
 appdb         | information_schema | foreign_server_options                | VIEW
 appdb         | information_schema | foreign_servers                       | VIEW
 appdb         | information_schema | foreign_table_options                 | VIEW
 appdb         | information_schema | foreign_tables                        | VIEW
 appdb         | information_schema | information_schema_catalog_name       | VIEW
 appdb         | information_schema | key_column_usage                      | VIEW
 appdb         | information_schema | parameters                            | VIEW
 appdb         | information_schema | referential_constraints               | VIEW
 appdb         | information_schema | role_column_grants                    | VIEW
 appdb         | information_schema | role_routine_grants                   | VIEW
 appdb         | information_schema | role_table_grants                     | VIEW
 appdb         | information_schema | role_udt_grants                       | VIEW
 appdb         | information_schema | role_usage_grants                     | VIEW
 appdb         | information_schema | routine_privileges                    | VIEW
 appdb         | information_schema | routines                              | VIEW
 appdb         | information_schema | schemata                              | VIEW
 appdb         | information_schema | sequences                             | VIEW
 appdb         | information_schema | sql_features                          | BASE TABLE
 appdb         | information_schema | sql_implementation_info               | BASE TABLE
 appdb         | information_schema | sql_parts                             | BASE TABLE
 appdb         | information_schema | sql_sizing                            | BASE TABLE
 appdb         | information_schema | table_constraints                     | VIEW
 appdb         | information_schema | table_privileges                      | VIEW
 appdb         | information_schema | tables                                | VIEW
 appdb         | information_schema | transforms                            | VIEW
 appdb         | information_schema | triggered_update_columns              | VIEW
 appdb         | information_schema | triggers                              | VIEW
 appdb         | information_schema | udt_privileges                        | VIEW
 appdb         | information_schema | usage_privileges                      | VIEW
 appdb         | information_schema | user_defined_types                    | VIEW
 appdb         | information_schema | user_mapping_options                  | VIEW
 appdb         | information_schema | user_mappings                         | VIEW
 appdb         | information_schema | view_column_usage                     | VIEW
 appdb         | information_schema | view_routine_usage                    | VIEW
 appdb         | information_schema | view_table_usage                      | VIEW
 appdb         | information_schema | views                                 | VIEW
(65 rows)

appdb=#

Some important views in schema information_schema.

information_schema. .tables

information_schema. views

information_schema.triggers

appdb=> \d information_schema.tables
                                 View "information_schema.tables"
            Column            |               Type                | Collation | Nullable | Default
------------------------------+-----------------------------------+-----------+----------+---------
 table_catalog                | information_schema.sql_identifier |           |          |
 table_schema                 | information_schema.sql_identifier |           |          |
 table_name                   | information_schema.sql_identifier |           |          |
 table_type                   | information_schema.character_data |           |          |
 self_referencing_column_name | information_schema.sql_identifier |           |          |
 reference_generation         | information_schema.character_data |           |          |
 user_defined_type_catalog    | information_schema.sql_identifier |           |          |
 user_defined_type_schema     | information_schema.sql_identifier |           |          |
 user_defined_type_name       | information_schema.sql_identifier |           |          |
 is_insertable_into           | information_schema.yes_or_no      |           |          |
 is_typed                     | information_schema.yes_or_no      |           |          |
 commit_action                | information_schema.character_data |           |          |

appdb=>

Listing tables

# \x off

# SELECT table_catalog, table_schema, table_type FROM information_schema.tables
WHERE table_name in ('city', 'street', 'cash_box');

appdb=> \x off
Expanded display is off.
appdb=> SELECT table_catalog, table_schema, table_type FROM information_schema.tables
appdb-> WHERE table_name in ('city', 'street', 'cash_box');
 table_catalog | table_schema | table_type
---------------+--------------+------------
 appdb         | public       | BASE TABLE
 appdb         | public       | BASE TABLE
 appdb         | fin          | BASE TABLE
(3 rows)

appdb=>

Listing tables

# \x off

# SELECT table_catalog, table_schema, table_type FROM information_schema.tables
WHERE table_name in ('city', 'street', 'cash_box');

appdb=> \x off
Expanded display is off.
appdb=> SELECT table_catalog, table_schema, table_type FROM information_schema.tables
appdb-> WHERE table_name in ('city', 'street', 'cash_box');
 table_catalog | table_schema | table_type
---------------+--------------+------------
 appdb         | public       | BASE TABLE
 appdb         | public       | BASE TABLE
 appdb         | fin          | BASE TABLE
(3 rows)

appdb=>

Listing source code of a function

\sf

Editing source code of a function

\ef

Creating Trigger Functions
Extracting DDL of Objects

Leave a Comment

Your email address will not be published. Required fields are marked *