PostgreSQL ERROR: relation "geometry_columns" does not exist

[michaeljp]

Error:"Error SQLPrepare: [Simba][PostgreSQL] (30) Error occurred while trying to execute a query"

 

Environment Details

 

PostgreSQL data source is throwing the following after upgrading Alteryx Designer to 2022.3.

 

Error: Input Data (1): Error SQLPrepare: [Simba][PostgreSQL] (30) Error occurred while trying to execute a query: [SQLState 42P01] ERROR: relation "geometry_columns" does not exist LINE 1: SELECT SRID FROM GEOMETRY_COLUMNS WHERE F_TABLE_NAME 

 

• Alteryx Designer, Server 
  • 2022.3
• PostgreSQL
  • Database version prior to 11.7
• Driver
  • Simba PostgreSQL ODBC Driver 1.4.45.1001

image.png

Cause

 

PostgreSQL database version prior to 11.7 is not validated with Simba PostgreSQL ODBC Driver 1.4.45.1001

 
Spoilers

• All nodes in a multi-node environment will need to have ODBC driver and DSN changes applied
• The Information Schema GEOMETRY_COLUMNS table provides support for Spatial Reference systems for GIS data. https://postgis.net/docs/AddGeometryColumn.html

Resolution

 

Solution A

1. Identify database version
2. Obtain and install a matching driver for the database version from PostgreSQL.
3. Setup System DSN to use the alternate driver 

 

Solution B

1. Identify database version
2. Upgrade PostgreSQL database to 11.7+

 

Additional Resources

• PostgreSQL validated Driver 
• PostgreSQL ODBC Driver downloads
• Redshift  Error opening "SELECT SRID FROM GEOMETRY_COLUMNS