> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# pg_extension

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `pg_extension` <InternalLink path="system-catalogs">system catalogs</InternalLink> provides information about CockroachDB extensions.

## Data exposed by `pg_extension`

In CockroachDB v25.2, `pg_extension` contains the following tables, all of which provide information about CockroachDB's <InternalLink path="spatial-data-overview">spatial extension</InternalLink>:

* `geography_columns`
* `geometry_columns`
* `spatial_ref_sys`

<Note>
  `pg_extension` tables are read-only.
</Note>

To see the list of tables in `pg_extension` for the <InternalLink path="sql-name-resolution#current-database">current database</InternalLink>, use the following <InternalLink path="show-tables">`SHOW TABLES`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM pg_extension;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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 <InternalLink path="selection-queries">`SELECT` queries</InternalLink> 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 <InternalLink path="security-reference/authorization#admin-role">`admin` privileges</InternalLink>.
</Tip>

<Note>
  Unless specified otherwise, queries to `pg_extension` assume the <InternalLink path="sql-name-resolution#current-database">current database</InternalLink>.
</Note>

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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM movr.pg_extension.pg_indexes;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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

* <InternalLink path="show-vars">`SHOW`</InternalLink>
* <InternalLink path="show-databases">`SHOW DATABASES`</InternalLink>
* <InternalLink path="show-schemas">`SHOW SCHEMAS`</InternalLink>
* <InternalLink path="show-tables">`SHOW TABLES`</InternalLink>
* <InternalLink path="sql-name-resolution">SQL Name Resolution</InternalLink>
* <InternalLink path="system-catalogs">System Catalogs</InternalLink>