pg_extension

Warning:
Cockroach Labs will stop providing Assistance Support for this version on May 10, 2022. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

New in v20.2: The pg_extension system catalogs provides information about CockroachDB extensions.

Data exposed by pg_extension

In CockroachDB v20.2, pg_extension contains the following tables, all of which provide information about CockroachDB's spatial extension:

  • geography_columns
  • geometry_columns
  • spatial_ref_sys
Note:

pg_extension tables are read-only.

To see the list of tables in pg_extension for the current database, use the following SHOW TABLES statement:

icon/buttons/copy
> 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 SELECT queries on the tables in pg_extension.

Tip:

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

Note:

Unless specified otherwise, queries to pg_extension assume the current database.

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:

icon/buttons/copy
> SELECT * FROM movr.pg_extension.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

YesYes NoNo