Nisarg Upadhyay
Welcome screen of psqlODBC setup

Configure ODBC drivers for PostgreSQL

October 22, 2021 by

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.

Graphical user interface, text, application

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.

List of MSI files of drivers

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.

List of various pSQLODBC drivers

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.

Extract the pSQLodbc zip file

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.

Welcome screen of psqlODBC setup

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.

License terms

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.

Select features to install

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.

Ready to install

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

Driver has been installed

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.

ODBC data source administrator

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

Create new data source

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:

ODBC connetion parameters

Click on Test to verify the connectivity.

Connection to PostgreSQL has been created

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.

ODBC Data Source 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.

Nisarg Upadhyay
936 Views