PostgreSQL ODBC Driver 101: How to Connect, Configure & Troubleshoot?

PostgreSQL is an Object-Relational Database Management System that focuses on SQL compliance and extensibility. This Open-Source Relational Database supports both JSON & SQL querying and serves as the primary data source for numerous mobile, web, geospatial, and analytics applications.
This blog talks about the steps you can follow to configure the PostgreSQL ODBC Driver connection seamlessly. It also gives a brief introduction to ODBC Drivers and PostgreSQL before diving into the steps involved in PostgreSQL ODBC Driver Configuration.

What is PostgreSQL?

PostgreSQL is widely known for its Open-Source platform that supports all features of RDBMS. It also provides an array of features on top of RDBMS features like indexes, stored procedures, views, triggers, and atomicity features to name a few.

What are ODBC Drivers?

ODBC Drivers leverage the Open Database Connectivity Interface offered by Microsoft. Subsequently, it allows applications to extract data in Database Management Systems (DBMS) by utilizing SQL as a standard for extracting the data. ODBC offers maximum compatibility, which means a single application can link to various DBMS. After this, application users can add ODBC Database drivers to connect to the application of their choice of DBMS.

What is the Difference between ODBC & JDBC Drivers?

Aspect	JDBC Drivers	ODBC Drivers
Supported Languages	Java only	Multiple languages (Java, C, C++, etc.)
Platform Dependency	Independent	Dependent due to conversions
Programming Paradigm	Object-oriented	Procedural
Complexity	Simpler code, easier to learn and run	Harder to master, complex to run
Best Use Case	Recommended for Java applications	Suitable for non-Java applications

What is the PostgreSQL PSQL ODBC Driver?   

PostgreSQL PSQL ODBC Driver is the official PostgreSQL ODBC Driver that allows data access similar to other vendor-specific drivers. Once you’ve written an ODBC application, you can easily connect to any specified back-end database, irrespective of the vendor, as long as the database schema remains intact with PostgreSQL ODBC Driver.
For instance, you could have PostgreSQL and MS SQL Servers that store the same data. By leveraging PostgreSQL ODBC Driver, you empower your Windows to make the same calls while the back-end data source resembles the one you have on hand from the viewpoint of the Windows application.

How to Connect with PostgreSQL using ODBC Drivers?

Here are the steps involved in configuring PostgreSQL ODBC Driver connection:

• Step 1: Download the PostgreSQL ODBC Driver for PostgreSQL
• Step 2: Install the PostgreSQL ODBC Driver
• Step 3: Use System DSN to Setup the psqlODBCx64 Driver
• Step 4: Connect and Verify PostgreSQL ODBC Connection

Step 1: Download the PostgreSQL ODBC Driver for PostgreSQL

• In this example, it is recommended that you use connection objects to connect to databases. First, you need to create a new channel to try out the PostgreSQL ODBC Driver code with the following credentials:
  • Source: From Translator
  • Name: DB PostgreSQL Connection
  • Destination: To Channel
• You can even leverage an existing channel that would require a Filter component or a From/To translator.
• Next, you need to connect to a database through ODBC. To do this, you can paste the following code snippet into the translator:

function main()
     local conn = db.connect{  
     api=db.POSTGRES,
     name='your_odbc_server_name',
     user='your_login',
     password='secret',
     use_unicode = true,
     live = true
  }
 
  conn:execute{sql='SELECT * FROM <your table>', live=true}
end

• Here, the Name refers to the name of an ODBC source that can be created via the ODBC Administrator. You can finally adapt the code to your specific requirements.

Step 2: Install the PostgreSQL ODBC Driver

• To set up a new ODBC data source for PostgreSQL, you first need to install the latest PostgreSQL ODBC Drivers. You can start by downloading the latest 64-bit driver installer from the psqlodbc download site.

• Next, double-click on the MSI file to install the driver. When you run the MSI file, the psqlODBC_x64 driver setup wizard kicks into motion. On the first screen, you can view the details as follows:

• When you reach the End-User Agreement window, you can look at the license details and agreement. Review the license terms and click on I accept the terms within the License Agreement. You can then print the agreement by clicking on the Print button.

• On the Custom Setup screen, you can choose the feature of the drivers. Within the ODBC Driver Setup, you can view the driver under the psqlODBC_x64 tree. You can take a look at the disk usage of the driver. If you wish to install the documentation, click on the button under the psqlODBC_x64 tree and choose the entire feature installed on the disk option as follows:

• When you’ve reached the ready to install screen, click on the Install button. If you wish to change or review the installation settings, click on the Back button.

• When you’ve completed the installation, click on Finish to close the Wizard.

Step 3: Use System DSN to Setup the psqlODBCx64 Driver

• Now, to configure the ODBC Data Source for the PostgreSQL database, open the ODBC Data Source (64-bit) and click on the System DSN tab. You can click on the Add button to move on to the next segment.

• A dialog box Create a New Data Source opens up. Choose the PostgreSQL Unicode (x64) driver and click on the Finish button to move ahead.

• Another dialog box PostgreSQL Unicode ODBC Driver Setup is opened. Within the dialog box, you need to specify various parameters like Description, Database, Data Source, Port, Server, SSL Mode, Username, and Password. After you’ve configured all the parameters, the configuration setup will resemble the following image:

Step 4: Connect and Verify PostgreSQL ODBC Connection

• After you’ve completed the PostgreSQL ODBC Driver setup, you can click on Test to verify the connectivity:

• As is evident from the previous window, the connection has been established successfully. Click on the Save button to create the system DSN. When you move back to the System DSN screen, you can view the pgadmin13 DSN has been generated.

Conclusion

This blog discusses the steps you can follow to set up a seamless PostgreSQL ODBC Driver connection. It also covers the basics of PostgreSQL and ODBC Drivers and how they differ from JDBC Drivers. It also covers the different remedies to common errors that you can encounter while trying to establish PostgreSQL ODBC Connections.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like PostgreSQL into your Data Warehouse or a destination like PostgreSQL to be visualized in a BI tool. Hevo is fully automated and, hence, does not require you to code.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

FAQs