• Introduction
• Connect to PostgreSQL Server
• Setup ODBC
• Troubleshooting
• More Information

Introduction

This article explains how to connect to PostgreSQL with the official PostgreSQL ODBC driver psqlodbc, when using Windows 10.

It covers the following topics:

1. How to connect to postrgreSQL (with code samples).
2. How to setup ODBC:
   • Configure ODBC using the ODBC Data Source Administrator:

     We recommend using system DSNs (available to all users).

Connect to PostgreSQL Server [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples.

Follow these steps to connect:

1. Create a new channel to try out the code:
   • Create a channel like this:
     • Name: DB PostgreSQL Connection (or similar)
     • Source: From Translator
     • Destination: To Channel
   • Alternatively use an existing channel:

     Your channel will need a From/To Translator or a Filter component.

2. Connect to a database using ODBC:
   • Paste this code into the Translator:

     If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote PostgreSQL database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

     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

   • The name is the name of an ODBC source that you create using the ODBC Administrator.
3. Adapt the code to your requirements.

Setup ODBC [top]

To set up a new ODBC data source for PostgreSQL:

1. Install the latest PostgreSQL ODBC drivers:
   • Download the latest 64 bit driver installer (zipped msi file) from the psqlodbc download site.
   • Unzip the file.
   • Run the msi installer and accept the defaults.
2. Open the 64 bit ODBC Administrator:

   Windows 10 (64 bit version) supports 32 and 64 bit ODBC sources — always use the 64 bit ODBC Administrator.

   • Search for ODBC and choose ODBC Data Sources (64 bit):
     
   • Right Click and choose Run as Administrator:
     
3. Open the System DSN tab and click Add:
   
4. Choose the latest PostgreSQL ODBC driver and click Finish:
   
5. Enter the ODBC credentials:
   • Use any Data Source and Description you prefer.
   • Optional: Choose your default Database.
   • Enter the Network Name of the PostgreSQL Server in the Server field:
     Note: If you are unsure of the name ask your DBA (database administrator).
   • Optional: Enter the Port number (default = 5432)
   • Enter the User name and Password:
     Note: If you are unsure of these ask your DBA (database administrator).
     
6. Test the data source connection:
   • Click Test:
     
   • And if it works you are good to go:
     
   • Click Save to create the ODBC data source:
     
7. If the connection does not work speak to your DBA (database administrator) about how to login to the database (user authentication).

Troubleshooting [top]

• Problems with connection settings:

  Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

• Iguana may not always support the very latest version of the psqlodbc ODBC drivers:

  Please contact us at support@interfaceware.com if you need to know the latest supported version of the psqlodbc ODBC drivers.

• Incorrect port number:

  SQL Server defaults to port 5432, but a different port can be used. Speak to your database administrator (DBA) or network administrator.

• Using integrated security:

  Using integrated security is the usual way to connect to a Microsoft SQL Server database. If this does not work speak to your DBA (database administrator).

If you need more help please contact us at support@interfaceware.com.

More Information [top]

• Using Database Connection Objects
• Forum thread on Windows ODBC
• Using Integrated Authentication, Microsoft SQL Server documentation (kerberos etc)
• Connect to PostgreSQL from Linux or Mac with ODBC