Question

Error creating PostGIS reader in FME


Badge +6

I get this error while creating PostGIS reader in both 2015 and 2017.1 FME versions.

Please advise for the below error.

ERROR |Error executing SQL command ('select version(), postgis_version()'): 'ERROR: function postgis_version() does not exist

LINE 1: select version(), postgis_version()

^

HINT: No function matches the given name and argument types. You might need t...'

2018-06-04 20:46:28| 2.4| 0.0|WARN |Error retrieving version for PostgreSQL database

2018-06-04 20:46:28| 2.4| 0.0|INFORM|Successfully closed POSTGIS database reader

2018-06-04 20:46:28| 2.4| 0.0|ERROR |Reader failed in open() call

Tagging in @takashi, @erik_jan, @TiaAtSafe, @Mark2AtSafe, @RylanAtSafe, @jlutherthomas


7 replies

Userlevel 2
Badge +17

Most likely you haven't created PostGIS extension onto the database yet. Make sure that the database has the PostGIS extension with pgAdmin.

0684Q00000ArJmXQAV.png

If not yet, you can create the extension with this SQL statement. Assuming, of course, PostGIS has been installed into your machine already.

create extension postgis;
Badge +6

Most likely you haven't created PostGIS extension onto the database yet. Make sure that the database has the PostGIS extension with pgAdmin.

0684Q00000ArJmXQAV.png

If not yet, you can create the extension with this SQL statement. Assuming, of course, PostGIS has been installed into your machine already.

create extension postgis;
Hi @takashi, we have already created PostGIS extension. 

 

 

  • One thing to note is that the different user was able to successfully create reader. 
  • These errors are only specific to one user, but need to be resolved.
Userlevel 4

Sounds like grant issues. Make sure your user has sufficient rights.

To debug, try first to give the user full rights on the database schema. If that works, it's definitely a rights issue and you need to backtrack to find what's missing.

Badge +11

Hi @fmeuser_gc,

It seems you are with a solution already. You mention in a reply to Takashi that you have one user that can successfully create the reader. Expanding on David_r, I would look at the differences between these users and see what might need to be granted to the user with the error.

Also, in case there is an an issue with FME and the version of PostgreSQL involved, can you confirm the version of PostgreSQL/PostGIS you are using here? And can you provide the SQL script used to create the user with the error and the SQL script to create the user that works. Thanks.

Badge +11

The user reported they were able to get it to work by updating the search_path for the user.

Badge

Hi. I have worked on PostGIS previously but never encountered this error of extension.

So, I was working on a database with postgis version 2.4.4 and was working fine. But, this new database is version 2.3.4 and showing me the below error:

Any solutions for this?

Thanks.

Badge

Assuming postGIS extension has been enabled and you are writing to a schema 'staging' for example setting the search path explicitly to the schema you are going to write to first and public second. If public is not included this will also produce this error as FME cannot access public where all the postgis functions are.

This can be achieved by setting the search path in the SQL to Run Before Write.

Capture_20210929 

Reply