pg_catalog

For PostgreSQL compatibility, CockroachDB includes a called pg_catalog. The tables in the pg_catalog schema roughly correspond to the system catalogs in PostgreSQL. pg_catalog tables are read-only.

Data exposed by pg_catalog

The tables in CockroachDB’s pg_catalog schema correspond to a subset of the virtual tables and views that make up the PostgreSQL system catalogs. Not all PostgreSQL system catalogs have a corresponding table in pg_catalog, and some of the pg_catalog tables are empty. See the following table for a detailed comparison between PostgreSQL 13 system catalogs and pg_catalog tables.

PostgreSQL 13 system catalog	pg_catalog table
pg_aggregate	pg_aggregate
pg_am	pg_am
pg_amop	pg_amop (empty)
pg_amproc	pg_amproc (empty)
pg_attrdef	pg_attrdef
pg_attribute	pg_attribute
pg_auth_members	pg_auth_members
pg_authid	pg_authid
pg_available_extension_versions	pg_available_extension_versions (empty)
pg_available_extensions	pg_available_extensions
pg_cast	pg_cast
pg_class	pg_class
pg_collation	pg_collation
pg_config	pg_config (empty)
pg_constraint	pg_constraint
pg_conversion	pg_conversion
	pg_cursors
pg_database	pg_database
pg_db_role_setting	pg_db_role_setting
pg_default_acl	pg_default_acl
pg_depend	pg_depend
pg_description	pg_description
pg_enum	pg_enum
pg_event_trigger	pg_event_trigger
pg_extension	pg_extension
pg_file_settings	pg_file_settings (empty)
pg_foreign_data_wrapper	pg_foreign_data_wrapper
pg_foreign_server	pg_foreign_server
pg_foreign_table	pg_foreign_table
pg_group	pg_group (empty)
pg_hba_file_rules	pg_hba_file_rules (empty)
pg_index	pg_index
pg_indexes	pg_indexes
pg_inherits	pg_inherits
pg_init_privs	pg_init_privs (empty)
pg_language	pg_language (empty)
pg_largeobject	pg_largeobject (empty)
pg_largeobject_metadata	pg_largeobject_metadata (empty)
pg_locks	pg_locks
pg_matviews	pg_matviews
pg_namespace	pg_namespace
pg_opclass	pg_opclass (empty)
pg_operator	pg_operator
pg_opfamily	pg_opfamily (empty)
pg_partitioned_table	pg_partitioned_table (empty)
pg_policies	pg_policies (empty)
pg_policy	pg_policy (empty)
pg_prepared_statements	pg_prepared_statements (empty)
pg_prepared_xacts	pg_prepared_xacts (empty)
pg_proc	pg_proc
pg_publication	pg_publication (empty)
pg_publication_rel	pg_publication_rel (empty)
pg_publication_tables	pg_publication_tables (empty)
pg_range	pg_range
pg_replication_origin	pg_replication_origin (empty)
pg_replication_origin_status	pg_replication_origin_status (empty)
pg_replication_slots	pg_replication_slots (empty)
pg_rewrite	pg_rewrite
pg_roles	pg_roles
pg_rules	pg_rules (empty)
pg_seclabel	pg_seclabel
pg_seclabels	pg_seclabels
pg_sequence	pg_sequence
pg_sequences	pg_sequences
pg_settings	pg_settings
pg_shadow	pg_shadow (empty)
pg_shdepend	pg_shdepend
pg_shdescription	pg_shdescription
pg_shmem_allocations	pg_shmem_allocations (empty)
pg_shseclabel	pg_shseclabel
pg_stat_activity	pg_stat_activity (empty)
pg_stat_all_indexes	pg_stat_all_indexes (empty)
pg_stat_all_tables	pg_stat_all_tables (empty)
pg_stat_archiver	pg_stat_archiver (empty)
pg_stat_bgwriter	pg_stat_bgwriter (empty)
pg_stat_database	pg_stat_database (empty)
pg_stat_database_conflicts	pg_stat_database_conflicts (empty)
pg_stat_gssapi	pg_stat_gssapi (empty)
pg_stat_progress_analyze	pg_stat_progress_analyze (empty)
pg_stat_progress_basebackup	pg_stat_progress_basebackup (empty)
pg_stat_progress_cluster	pg_stat_progress_cluster (empty)
pg_stat_progress_create_index	pg_stat_progress_create_index (empty)
pg_stat_progress_vacuum	pg_stat_progress_vacuum (empty)
pg_stat_replication	pg_stat_replication (empty)
pg_stat_slru	pg_stat_slru (empty)
pg_stat_ssl	pg_stat_ssl (empty)
pg_stat_subscription	pg_stat_subscription (empty)
pg_stat_sys_indexes	pg_stat_sys_indexes (empty)
pg_stat_sys_tables	pg_stat_sys_tables (empty)
pg_stat_user_functions	pg_stat_user_functions (empty)
pg_stat_user_indexes	pg_stat_user_indexes (empty)
pg_stat_user_tables	pg_stat_user_tables (empty)
pg_stat_wal_receiver	pg_stat_wal_receiver (empty)
pg_stat_xact_all_tables	pg_stat_xact_all_tables (empty)
pg_stat_xact_sys_tables	pg_stat_xact_sys_tables (empty)
pg_stat_xact_user_functions	pg_stat_xact_user_functions (empty)
pg_stat_xact_user_tables	pg_stat_xact_user_tables (empty)
pg_statio_all_indexes	pg_statio_all_indexes (empty)
pg_statio_all_sequences	pg_statio_all_sequences (empty)
pg_statio_all_tables	pg_statio_all_tables (empty)
pg_statio_sys_indexes	pg_statio_sys_indexes (empty)
pg_statio_sys_sequences	pg_statio_sys_sequences (empty)
pg_statio_sys_tables	pg_statio_sys_tables (empty)
pg_statio_user_indexes	pg_statio_user_indexes (empty)
pg_statio_user_sequences	pg_statio_user_sequences (empty)
pg_statio_user_tables	pg_statio_user_tables (empty)
pg_statistic	None
pg_statistic_ext	pg_statistic_ext (empty)
pg_statistic_ext_data	None
pg_stats	None
pg_stats_ext	None
pg_subscription	pg_subscription (empty)
pg_subscription_rel	pg_subscription_rel (empty)
pg_tables	pg_tables
pg_tablespace	pg_tablespace
pg_timezone_abbrevs	pg_timezone_abbrevs (empty)
pg_timezone_names	pg_timezone_names
pg_transform	pg_transform (empty)
pg_trigger	pg_trigger
pg_ts_config	pg_ts_config (empty)
pg_ts_config_map	pg_ts_config_map (empty)
pg_ts_dict	pg_ts_dict (empty)
pg_ts_parser	pg_ts_parser (empty)
pg_ts_template	pg_ts_template (empty)
pg_type	pg_type
pg_user	pg_user
pg_user_mapping	pg_user_mapping
pg_user_mappings	pg_user_mappings (empty)
pg_views	pg_views

To list the tables in pg_catalog for the , use the following statement:

> SHOW TABLES FROM pg_catalog;

  schema_name |       table_name        | type  | owner | estimated_row_count
--------------+-------------------------+-------+-------+----------------------
  pg_catalog  | pg_aggregate            | table | NULL  |                NULL
  pg_catalog  | pg_am                   | table | NULL  |                NULL
  ...

To prohibit queries against empty tables, set the stub_catalog_tables to off.

Query pg_catalog tables

You can run on the tables in pg_catalog.

To ensure that you can view all of the tables in pg_catalog, query the tables as a user with .

Unless specified otherwise, queries to pg_catalog assume the .

For example, to return the pg_catalog table with additional information about indexes in , you can query the pg_catalog.pg_indexes table:

> SELECT * FROM movr.pg_catalog.pg_indexes;

   crdb_oid  | schemaname |         tablename          |                   indexname                   | tablespace |                                                            indexdef
-------------+------------+----------------------------+-----------------------------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------
  2055313241 | public     | users                      | users_pkey                                    | NULL       | CREATE UNIQUE INDEX users_pkey ON movr.public.users USING btree (city ASC, id ASC)
  1795576970 | public     | vehicles                   | vehicles_pkey                                 | NULL       | CREATE UNIQUE INDEX vehicles_pkey ON movr.public.vehicles USING btree (city ASC, id ASC)
  1795576969 | public     | vehicles                   | vehicles_auto_index_fk_city_ref_users         | NULL       | CREATE INDEX vehicles_auto_index_fk_city_ref_users ON movr.public.vehicles USING btree (city ASC, owner_id ASC)
   450499963 | public     | rides                      | rides_pkey                                    | NULL       | CREATE UNIQUE INDEX rides_pkey ON movr.public.rides USING btree (city ASC, id ASC)
   450499960 | public     | rides                      | rides_auto_index_fk_city_ref_users            | NULL       | CREATE INDEX rides_auto_index_fk_city_ref_users ON movr.public.rides USING btree (city ASC, rider_id ASC)
   450499961 | public     | rides                      | rides_auto_index_fk_vehicle_city_ref_vehicles | NULL       | CREATE INDEX rides_auto_index_fk_vehicle_city_ref_vehicles ON movr.public.rides USING btree (vehicle_city ASC, vehicle_id ASC)
  2315049508 | public     | vehicle_location_histories | vehicle_location_histories_pkey               | NULL       | CREATE UNIQUE vehicle_location_histories_pkey ON movr.public.vehicle_location_histories USING btree (city ASC, ride_id ASC, "timestamp" ASC)
   969972501 | public     | promo_codes                | promo_codes_pkey                              | NULL       | CREATE UNIQUE INDEX promo_codes_pkey ON movr.public.promo_codes USING btree (code ASC)
   710236230 | public     | user_promo_codes           | user_promo_codes_pkey                         | NULL       | CREATE UNIQUE INDEX user_promo_codes_pkey ON movr.public.user_promo_codes USING btree (city ASC, user_id ASC, code ASC)
(9 rows)

See also