Postgresql odbc drivers for windows

In this article, we are going to understand the process to configure the ODBC driver for PostgreSQL. For the
demonstration, I have installed PostgreSQL on my workstation. The details of the server and database are following:

1. Server Name: PostgreSQL 13
2. Port Number: 5432
3. Database Name: DemoDatabase
4. Username: Postgres
5. SSL: No

You can view the details in pgadmin4. The screenshot of the configuration is following.

I have divided the article into three parts. The details are following:

1. First Part: In the first part we will learn how we can download the recent and 64-bit version of the psqlODBC_x64 driver
2. Second Part: In the second part we will learn how we can install the psqlODBC_x64 driver
3. Third part: In the third part, we will learn how we can configure the system DSN using the pSQLODBC_x64 driver and configure the required parameters

First, let us begin with the download process of the latest version of the psqlodbc_x64 driver.

Download pSQLODBC_x64 Driver

You can download the ODBC driver for PostgreSQL from the official download website of PostgreSQL. You can view the
various versions of the drivers from this location. We are configuring the ODBC driver for windows 10, so we are going to download the MSI file of the Driver. Click on the MSI folder.

Under the MSI directory, you can view the various versions of Driver. The files are compressed in zip format. We
want to download the latest version, so scroll down to the bottom of the page and click on the psqlodbc_13_01_0000-x64.zip file.

The download begins. Once download completes, right-click on psqlodbc_13_01_0000-x64.zip file and select Extract to psqlodbc_13_01_0000-x64 option.

Once the MSI file is downloaded, double-click on the MSI file to install the Driver.

Install the psqlODBC_x64 driver

When we run the MSI file, the pSQLODBC_x64 diver setup wizard begins. On the first screen, you can see the details of the wizard.

On End-User License Agreement, you can view the license agreement and details. Review the license terms and click on I accept the terms in the license agreement. You can print the agreement by clicking on the Print button.

On the Custom Setup screen, you can select the feature of the drivers. In the ODBC driver setup,
you can see the driver under the pSQLODBC_x64 tree. You can view the disk usage of the Driver. If you want to install the documentation, click on the button under the psqlODBC_x64 tree and select the entire feature installed
on the disk option. We are installing the documentation, so I have chosen that option.

On ready to install screen, click on the Install button. If you want to review or make any changes in installation settings, click on Back.

Once installation completes, click on Finish to close the wizard.

The pSQLODBC_x64 Driver has been installed successfully. Now, let us configure the Driver in ODBC data source to use it.

Configure pSQLODBC_x64 Driver using System DSN

To configure the ODBC data source for the PostgreSQL database, open ODBC Data Source (64–bit) 🡪 Click on System DSN tab 🡪 Click on Add.

A dialog box Create a new data source opens. Select PostgreSQL Unicode(x64) driver and click on Finish.

Another dialog box PostgreSQL Unicode ODBC Driver (pSQLODBC) Setup opens. In the dialog box, you must specify the following parameters:

1. Data Source: Specify the desired Data Source name. The name will be used to identify the DSN. I have given pgadmin13 as DSN
2. Description: Provide the details of the data source
3. Database: Specify the database that you want to use. In our configuration, we are using DemoDatabase, so the database name is DemoDatabase
4. Server: Specify the Server name/hostname on which the PostgreSQL is installed. We have installed PostgreSQL on localhost so the server name is localhost
5. Port: Specify the port number on which the PostgreSQL services are running. PostgreSQL service is running on 5432 port so the value of the port number is 5432
6. SSL Mode: If you are using SSL to connect to PostgreSQL, then specify the SSL Mode. We are not using SSL to connect to the PostgreSQL, so I have chosen disabled
7. Username and Password: Specify the appropriate username and password to connect to PostgreSQL. We are using the Postgres user to connect

Once all parameters are configured, the configuration setup looks like the following image:

Click on Test to verify the connectivity.

As you can see, the connection has been established successfully. Click on Save to create the system DSN. Back to the System DSN screen, you can see the pgadmin13 DSN has been created.

Summary

In this article, we learned how we could use the PostgreSQL ODBC driver. We learned the following:

1. How to download the psqlODBC_x64 driver from the official website of pgSQLODBC_x64 Driver for windows 10
2. How to install the pgODBC_x64 Driver on the workstation
3. How to configure the PostgreSQL Unicode ODBC driver using ODBC data source administrator

In the next article, I will explain how we can use the pgODBC_x64 Driver to create an SSRS report and import/export PostgreSQL data to an excel file.

Devart ODBC Driver for PostgreSQL provides a high-performance and feature-rich connectivity solution for ODBC-compliant applications to access PostgreSQL databases from Windows, macOS, and Linux, both 32-bit and 64-bit. Apple Silicon M1 is supported. Full support for standard ODBC API functions and data types implemented in our driver makes interaction of your application with PostgreSQL fast, easy and extremely handy.
Available in both installer formats, MSI and EXE.

Key Features

Direct Connection

Our connectivity solution enables various ODBC-aware applications to establish a direct connection to PostgreSQL via TCP/IP to eliminate the need for PostgreSQL libraries or clients. Direct connection increases the speed of data transmission between an external application and PostgreSQL, which is crucial for data analysis in real time. It also streamlines the deployment process, since there is no need to distribute any additional libraries or client tools with the driver.

Cost-Effective Deployment

Our ODBC driver is a standalone installation file that doesn’t require the user to deploy and configure any additional software such as a database client or a vendor library. Deployment costs are reduced drastically, especially when using the silent install method with an OEM license in large organizations that have hundreds of machines.

Secure Connection

Work with PostgreSQL securely using SSL connection options provided in our driver along with the support for SSL protocol. You can also connect to PostgreSQL securely via SSH tunnel. SSH protocol is one of the most reliable protocols for data encryption.

Advanced Data Conversion

We have implemented advanced Data Conversion mechanisms that provide bi-directional mapping between any PostgreSQL and ODBC data types.

ODBC Conformance

The driver provides full support for common ODBC interface:

• ODBC API Functions support
• ODBC Data Types support
  Additionally, we support Advanced Connection String parameters to allow desktop or web applications to connect to PostgreSQL from various environments and platforms that support ODBC.

Bulk Updates

Moreover, with our driver you can perform bulk updates to PostgreSQL database by combining SQL statements into batches, thus simplifying and speeding up large data modification to PostgreSQL.

PostgreSQL Compatibility

Our driver is compatible with the latest server versions, up to PostgreSQL 13. We also provide compatibility with Microsoft Azure Database for PostgreSQL, Amazon Aurora and Google Cloud for PostgreSQL.

High Performance

Every operation with PostgreSQL becomes significantly faster using such capabilities of our driver as local data caching, connection pooling, and much more.

Support

Visit our Support page to get instant support from experienced professionals, fast and detailed responses, user engagement and interaction, frequent builds with bug fixes, and much more.

This article will guide you to install PostgreSQL ODBC Driver 64bit on Windows 10. Some software rely on ODBC in order to communicate with the PostgreSQL server. The PostgreSQL driver does not available on Windows. So, we need to download and install it manually. There are few steps to do this.

Steps to Install PostgreSQL ODBC Driver 64 bit on Windows 10

First, download the driver from the link below. Choose the version and architecture you want to install. In this example, I am going to install the 64bit edition.

https://www.postgresql.org/ftp/odbc/versions/msi/

I choose to install the latest version 12.01. I got the following file: psqlodbc_12_01_0000-x64.zip.

Next, extract the file and then double click the .exe file. The installation wizard will open up. Follow the steps until finish.

Create a new Data Source

After we install the driver, we need to create a new data source. To create a new data source, follow these steps.

At this points, we have successfully installed the PostgreSQL ODBC Driver and create a new ODBC connection on Windows 10.

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. Oracle has established itself as one of the largest vendors of RDBMS (Relational Database Management System) in the IT market since its inception. The query language that can be used to access data in Oracle’s relational databases is SQL.

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.

Table of Contents

• What is PostgreSQL?
  • Key Features of PostgreSQL
• What are ODBC Drivers?
• What is the Difference between ODBC & JDBC Drivers?
• What is the PostgreSQL PSQL ODBC Driver?
• How to Connect with PostgreSQL using ODBC Drivers?
  • 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
• How to Fix Common Errors with PostgreSQL ODBC Connections?
  • Working with Integrated Security
  • Fixing Connection Configurations
  • Fixing Wrong Port Number Issue
• Conclusion

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.

PostgreSQL runs on various major platforms like Windows, Linux, macOS, and UNIX to name a few. Concurrency is managed by PostgreSQL through Multi-Version Concurrency Control (MVCC). MVCC provides each transaction a “Database Snapshot” that allows changes to be made without impacting other transactions. This ensures the maintenance of ACID principles while doing away with the need to read locks.

Key Features of PostgreSQL

• Customizable: PostgreSQL can be customized by developing plugins to make the Database Management System fit your requirements. PostgreSQL also allows you to incorporate custom functions that are made with other programming languages like Java, C, C++, etc.
• Long History: PostgreSQL has been around for more than 30 years, having been initially released in 1988.
• Frequent Updates: The most recent PostgreSQL update was Version 13.1 on 12 November 2020.  
• MVCC Features: PostgreSQL happens to be the first Database Management System to implement Multi-Version Concurrency Control (MVCC) features.
• A Supportive Community: A dedicated community is always at your disposal. Private, third-party support services are available as well. The community updates the PostgreSQL platform via the PostgreSQL Global Development Group.
• Open-Source: This is an Object-Relational Database Management System(ORDBMS). This allows PostgreSQL to provide Object-Oriented and Relational Database functionality. PostgreSQL is a free and Open-Source ORDBMS.
• Users: PostgreSQL users include Apple, Cisco, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, Yahoo 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.

Here is what the ODBC Driver Interface describes:

• A customary way to link and log in to a DBMS.
• A customary set of error codes.
• SQL syntax based on the SQL Access Group SQL CAE Specification and X/Open.
• A customary way representation of data types.
• There are various types of ODBC function calls in its library:
  • Call Level Interface Specification.
  • Core functions based on the SQL Access Group and the X/Open.
  • Extended functions that provide support for supplementary functionalities, including scrollable cursors.

Thus ODBC supplies a customary medium to allow vendors of database drivers and application developers to switch data between data sources and applications.

What is the Difference between ODBC & JDBC Drivers?

You can only leverage JDBC Drivers to work with Java languages whereas ODBC Drivers can be used for any language like Java, C, C++, etc. JDBC Drivers are typically object-oriented whereas ODBC Drivers are procedural. It is not suggested to leverage ODBC Drivers for Java applications since it can end up hindering the performance due to internal conversions. This would lead to the applications becoming platform dependent which hurts the efficiency. So, it is suggested that you leverage a JDBC Driver for Java applications since these issues aren’t encountered while utilizing a JDBC Driver.

Apart from this, the code for JDBC is fairly simple to learn and can be run easily. In contrast, the code for ODBC Drivers is pretty hard to master and is complex to run as well.  

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 in 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, you can unzip the file and run the MSI installer to accept the default conditions.

• 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 ODBC Data Source (64-bit) followed by clicking on the System DSN tab. You can click on 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 as follows:

How to Fix Common Errors with PostgreSQL ODBC Connections?

Here are a few ways that can allow you to troubleshoot the common errors you might encounter when setting up a PostgreSQL ODBC Driver connection:

Working with Integrated Security

You can leverage integrated security to connect to a Microsoft SQL Server database. If this does not work, you can have a word with your DBA.

Fixing Connection Configurations

To fix connection configurations, you can have a word with your DBA (Database Administrator). These issues include problems with the username, password, Database Server Network Name, among others.

Fixing Wrong Port Number Issue

SQL Server generally works on Port 5432, but you are free to work on a different port of your choosing. You can speak with your Network Administrator or DBA for the same.

Conclusion

This blog discusses the steps you can follow to set up a PostgreSQL ODBC Driver connection seamlessly. 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.

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources such as PostgreSQL to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first handYou can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!