How to determine if postgis is enabled on a database?

Question

I wanted to know if there is a way to determine that PostGis was enabled on a database.

I am trying to replicate my production server with my dev machine and I am not sure if the database on my dev machine had either PostGIS or postgis_topology enabled or both.

I tried looking around for a solution but could not come up with anything.
Any suggestions in this regard would be helpful.

Possible duplicate of [Using psql how do I list extensions installed in a database?](https://stackoverflow.com/questions/21799956/using-psql-how-do-i-list-extensions-installed-in-a-database) — , Nov 26 '18 at 10:06

score 15 · Accepted Answer · answered Nov 24 '18 at 23:59

Assuming that you have access to your database (either through the terminal or the pgadmin application), try the following:

Connect to your database
Run one (or both in order if you like) of the following queries:
```
SELECT PostGIS_version();
SELECT PostGIS_full_version();
```

if no error occurs, then you have PostGIS enabled for that database.

References:

score 5 · Answer 2 · answered Jul 24 '20 at 02:09

I'd just like to add to John Moutafis' response since at this point I can't comment on an answer.

My situation was a bit different because I had created the postgis extension for a different schema, let's call it schema_name:

CREATE EXTENSION postgis SCHEMA schema_name

Just running the accepted answer's query gave me an error stating that "No function matches the given name and argument types". Instead I had to make sure to add the schema name prior to the function call, as depicted below:

SELECT schema_name.PostGIS_version();
SELECT schema_name.PostGIS_full_version();

That ended up working for me.

score 1 · Answer 3 · answered Jul 24 '22 at 13:31

1

Check if some postgis(postgis_version or else) routines has already created.

SELECT COUNT(1) FROM information_schema.routines
WHERE routine_name = 'postgis_version'

The result "1" means postgis has already installed.

answered Jul 24 '22 at 13:31

Mehdi

520
1
5
9

score 1 · Answer 4 · answered Jan 25 '23 at 15:30

I would like to add that on Ubuntu 22.04 the PostGIS extension is added thus.

psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# create extension postgis;

CREATE EXTENSION

postgres=# select PostGIS_VERSION();

            postgis_version            

---------------------------------------

 3.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

(1 row)

Or use the following

postgres=# \dx

                                List of installed extensions

  Name   | Version |   Schema   |                        Description                         

---------+---------+------------+------------------------------------------------------------

 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

 postgis | 3.2.0   | public     | PostGIS geometry and geography spatial types and functions

(2 rows)

score 0 · Answer 5 · answered Jan 25 '23 at 14:13

Try one of these

postgres=# \dx
                        List of installed extensions
   Name    | Version |   Schema   |               Description
-----------+---------+------------+-----------------------------------------
 adminpack | 2.1     | pg_catalog | administrative functions for PostgreSQL
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)


postgres=# select * from pg_extension;
  oid  |  extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+-----------+----------+--------------+----------------+------------+-----------+--------------
 13535 | plpgsql   |       10 |           11 | f              | 1.0        |           |
 16384 | adminpack |       10 |           11 | f              | 2.1        |           |
(2 rows)

Looks like no PostGIS on this machine :-)

How to determine if postgis is enabled on a database?

5 Answers5