On this page 
Warning:
CockroachDB v20.2 is no longer supported as of May 10, 2022. For more details, refer to the Release Support Policy.
For PostgreSQL compatibility, CockroachDB includes a system catalog schema 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 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. 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 |
None |
pg_amproc |
None |
pg_attrdef |
pg_attrdef |
pg_attribute |
pg_attribute |
pg_auth_members |
pg_auth_members |
pg_authid |
pg_authid |
pg_available_extension_versions |
None |
pg_available_extensions |
pg_available_extensions |
pg_cast |
pg_cast |
pg_class |
pg_class |
pg_collation |
pg_collation |
pg_config |
None |
pg_constraint |
pg_constraint |
pg_conversion |
pg_conversion |
pg_cursors |
None |
pg_database |
pg_database |
pg_db_role_setting |
None |
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 |
None |
pg_foreign_data_wrapper |
pg_foreign_data_wrapper |
pg_foreign_server |
pg_foreign_server |
pg_foreign_table |
pg_foreign_table |
pg_group |
None |
pg_hba_file_rules |
None |
pg_index |
pg_index |
pg_indexes |
pg_indexes |
pg_inherits |
pg_inherits |
pg_init_privs |
None |
pg_language |
pg_language (empty) |
pg_largeobject |
None |
pg_largeobject_metadata |
None |
pg_locks |
pg_locks |
pg_matviews |
pg_matviews |
pg_namespace |
pg_namespace |
pg_opclass |
None |
pg_operator |
pg_operator |
pg_opfamily |
None |
pg_partitioned_table |
None |
pg_policies |
None |
pg_policy |
None |
pg_prepared_statements |
pg_prepared_statements (empty) |
pg_prepared_xacts |
pg_prepared_xacts (empty) |
pg_proc |
pg_proc |
pg_publication |
None |
pg_publication_rel |
None |
pg_publication_tables |
None |
pg_range |
pg_range |
pg_replication_origin |
None |
pg_replication_origin_status |
None |
pg_replication_slots |
None |
pg_rewrite |
pg_rewrite |
pg_roles |
pg_roles |
pg_rules |
None |
pg_seclabel |
pg_seclabel |
pg_seclabels |
pg_seclabels |
pg_sequence |
pg_sequence |
pg_sequences |
None |
pg_settings |
pg_settings |
pg_shadow |
None |
pg_shdepend |
pg_shdepend (empty) |
pg_shdescription |
pg_shdescription |
pg_shmem_allocations |
None |
pg_shseclabel |
pg_shseclabel |
pg_stat_activity |
pg_stat_activity |
pg_statistic |
None |
pg_statistic_ext |
None |
pg_statistic_ext_data |
None |
pg_stats |
None |
pg_stats_ext |
None |
pg_subscription |
None |
pg_subscription_rel |
None |
pg_tables |
pg_tables |
pg_tablespace |
pg_tablespace |
pg_timezone_abbrevs |
None |
pg_timezone_names |
None |
pg_transform |
None |
pg_trigger |
pg_trigger |
pg_ts_config |
None |
pg_ts_config_map |
None |
pg_ts_dict |
None |
pg_ts_parser |
None |
pg_ts_template |
None |
pg_type |
pg_type |
pg_user |
pg_user |
pg_user_mapping |
pg_user_mapping |
pg_user_mappings |
None |
pg_views |
pg_views |
To list the tables in pg_catalog for the current database, use the following SHOW TABLES 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
pg_catalog | pg_attrdef | table | NULL | NULL
...
Query pg_catalog tables
You can run SELECT queries on the tables in pg_catalog.
Tip:
To ensure that you can view all of the tables in pg_catalog, query the tables as a user with admin privileges.
Note:
Unless specified otherwise, queries to pg_catalog assume the current database.
For example, to return the pg_catalog table with additional information about indexes in movr database, 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 | primary | NULL | CREATE UNIQUE INDEX "primary" ON movr.public.users USING btree (city ASC, id ASC)
1795576970 | public | vehicles | primary | NULL | CREATE UNIQUE INDEX "primary" 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 | primary | NULL | CREATE UNIQUE INDEX "primary" 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 | primary | NULL | CREATE UNIQUE INDEX "primary" ON movr.public.vehicle_location_histories USING btree (city ASC, ride_id ASC, "timestamp" ASC)
969972501 | public | promo_codes | primary | NULL | CREATE UNIQUE INDEX "primary" ON movr.public.promo_codes USING btree (code ASC)
710236230 | public | user_promo_codes | primary | NULL | CREATE UNIQUE INDEX "primary" ON movr.public.user_promo_codes USING btree (city ASC, user_id ASC, code ASC)
(9 rows)
See also
Was this helpful?
