Connect to PostgreSQL from Windows with ODBC

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]

Note: Iguana may not always support the very latest version of the PostgreSQL ODBC drivers, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

If you need to know which versions of PostgreSQL are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

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):
      search for ODBC
    • Right Click and choose Run as Administrator:
      run as administrator
  3. Open the System DSN tab and click Add:
    odbc system dsn
  4. Choose the latest PostgreSQL ODBC driver and click Finish:
    choose postresql odbc driver
  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).
      odbc credentials postgresql
  6. Test the data source connection:
    • Click Test:
      test data source
    • And if it works you are good to go:
      test ODBC success
    • Click Save to create the ODBC data source:
      save ODBC 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]

Leave A Comment?