pg_extension

The pg_extension provides information about CockroachDB extensions.

Data exposed by pg_extension

In CockroachDB v23.2, pg_extension contains the following tables, all of which provide information about CockroachDB’s :

• geography_columns
• geometry_columns
• spatial_ref_sys

pg_extension tables are read-only.

To see the list of tables in pg_extension for the , use the following statement:

> SHOW TABLES FROM pg_extension;

  schema_name  |    table_name     | type  | owner | estimated_row_count
---------------+-------------------+-------+-------+----------------------
  pg_extension | geography_columns | table | NULL  |                NULL
  pg_extension | geometry_columns  | table | NULL  |                NULL
  pg_extension | spatial_ref_sys   | table | NULL  |                NULL
(3 rows)

Querying pg_extension tables

You can run on the tables in pg_extension.

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

Unless specified otherwise, queries to pg_extension assume the .

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

> SELECT * FROM movr.pg_extension.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