ERROR: PostGIS is already installed in schema 'public', uninstall it first

[edzer]

I tried to run the examples of pgInsert, after opening a connection with

conn = dbConnect(PostgreSQL(), dbname = "postgis")

but get the following errors:

...
>      pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf)
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  PostGIS is already installed in schema 'public', uninstall it first

does this suggest that you assume the database is not a PostGIS database?

[dnbucklin]

The functions use pgPostGIS to check if the PostGIS extension is installed in the database (if it isn't, then it tries to run the SQL CREATE EXTENSION postgis;

Try to run pgPostGIS(conn) and see if you get the same error.

I'm guessing that maybe you have an older version of PostgreSQL/PostGIS installed, based on this reference:

http://gis.stackexchange.com/questions/112592/installed-postgis-extension-not-listed-for-database

[edzer]

(your link is missing)

select PostGIS_full_version(); gives me

 POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.1.0, released 2016/04/25" LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY RASTER

and pgPostGIS(conn) gives me

Installing PostGIS extension version 2.2.2:
Query executed:
CREATE EXTENSION postgis;
--
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  PostGIS is already installed in schema 'public', uninstall it first
)

[basille]

Hey Edzer,

Thanks for the feedback! Can you give us the result of:

SELECT * FROM pg_available_extensions
WHERE name LIKE 'postgis';

Thanks.

[edzer]

  name   | default_version | installed_version |                               comment                               
---------+-----------------+-------------------+---------------------------------------------------------------------
 postgis | 2.2.2           |                   | PostGIS geometry, geography, and raster spatial types and functions

[dnbucklin]

Hi Edzer,
The result of your previous query is odd, given that PostGIS is installed (based on your SELECT postgis_full_versions(); result. Did you install/upgrade PostGIS from source, or use the method

CREATE EXTENSION postgis

to enable Postgis? If it is standard for "installed_version" to be empty when PostGIS is actually installed (but not through the CREATE EXTENSION method, we will have to account for that.

Also could you provide your Postgresql version? (select version();)

David

[edzer]

                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

Looking at the dates of files, I believe I installed with the following script:

# script to create a postgis-enabled database
USER=edzer
DB=postgis
DIR=/usr/share/postgresql/9.3/contrib/postgis-2.2
dropdb --if-exists $DB
createdb $DB
createlang plpgsql $DB

psql -d $DB -f $DIR/postgis.sql
psql -d $DB -f $DIR/postgis_comments.sql
psql -d $DB -f $DIR/spatial_ref_sys.sql
psql -d $DB -f $DIR/rtpostgis.sql
psql -d $DB -f $DIR/raster_comments.sql
psql -d $DB -f $DIR/topology.sql
psql -d $DB -f $DIR/topology_comments.sql

[dnbucklin]

Ok, thanks. The method for enabling PostGIS since PostgreSQL 9.1/PostGIS 2.0 is using the CREATE EXTENSION postgis; call:

http://postgis.net/install/

Which will register it correctly in the pg_extension catalog and allow for easy upgrades using (e.g.):

ALTER EXTENSION postgis 
 UPDATE TO "2.2.x";

Since your situation appears to be normal when installing by calling the script directly, I'll alter rpostgis to account for this situation...

David

[edzer]

Maybe close this when you're done?

[dnbucklin]

issue resolved on master (tag = v1.0.1), install using:

library(devtools)
install_github("mablab/rpostgis")