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:
- Server Name: PostgreSQL 13
- Port Number: 5432
- Database Name: DemoDatabase
- Username: Postgres
- 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:
- First Part: In the first part we will learn how we can download the recent and 64-bit version of the psqlODBC_x64 driver
- Second Part: In the second part we will learn how we can install the psqlODBC_x64 driver
- 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:
- Data Source: Specify the desired Data Source name. The name will be used to identify the DSN. I have given pgadmin13 as DSN
- Description: Provide the details of the data source
- Database: Specify the database that you want to use. In our configuration, we are using DemoDatabase, so the database name is DemoDatabase
- Server: Specify the Server name/hostname on which the PostgreSQL is installed. We have installed PostgreSQL on localhost so the server name is localhost
- 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
- 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
- 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:
- How to download the psqlODBC_x64 driver from the official website of pgSQLODBC_x64 Driver for windows 10
- How to install the pgODBC_x64 Driver on the workstation
- 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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022