Connecting to PostgreSQL with Alteryx

PostgreSQL, or simply Postgres, is an open-source SQL relational database management system. Connecting to a Postgres database can be a bit challenging if you’ve never done anything similar before. Today, I’m going to teach you how to connect to, append rows to, and update rows in a Postgres database using Alteryx.

Step 1: Download and Install the ODBC Drivers

The first step is to download the ODBC drivers to connect to a Postgres server. You can find a link to download the driver from their official website. Download the latest version, unzip the file, and run the executable to install.

Step 2: Configure ODBC Data Source

  • Once installed, open up the ODBC Data Sources app (search for it in the Windows search bar).
  • Under the User tab, click on Add.
  • Select the appropriate PostgreSQL driver and click on Finish. In my case, this was Unicode (x64).
  • A window will pop up asking for connection information. Finding this info depends on the database service you’re using, but generally, it’s listed in the settings or info tabs. A connection string URI (URL) might look like:
    postgres://[USERNAME]:[PASSWORD]@[HOST/SERVER]:[PORT]/[DATABASE]

    The port is usually 5432. For Supabase, I found this information in Project Settings > Database.

  • The Data Source and Description fields are for organizing your connections, so name them as you like.
  • Once the information is entered, click on Test to ensure it works, then Save.

Step 3: Connect in Alteryx

  • In Alteryx, go to Options > Advanced Options > Manage Data Connections.
  • Hover over Add Connection then click on Other.
  • Name the connection, then click the down arrow on the right of Connection String.
  • Select ODBC, and for Data Source Name, select the connection you made before.
  • Enter the same username and password as before, then click OK.
  • Now, you can find the connection in the Input or Output Tool by clicking Set Up a Connection and then ⭐ Saved.
  • Well done! You’re now connected.

Appending a New Row

Now that you’ve connected to the database, you might want to append a new row to a table. If the table has an ID field that should be unique and automatically assigned (i.e., you don’t want to manually add an ID value), follow these steps:

  1. Verify Database Setup: Ensure the database is set up to auto-assign IDs by inserting a new row and checking if the new row is assigned an ID. (Setting up the table is outside this blog’s scope, but there’s plenty of online material to help you.)
  2. Connect an Output Tool: Connect to the saved connection, ensuring the table name in the database and the filename you typed in are the same.
  3. Set Output Options to “Append Existing”: Then, click on the “…” to the right of Append Field Map, select Custom Mapping, and make sure NOTHING IS MAPPED TO ID. If anything is mapped to ID, that value will become the ID for the new row.

Updating a Row

  • Connect an Output Tool to the database in your saved connections.
  • Ensure the filename matches the table name in the database.
  • For Output Options, select Update (choose as you like; I wouldn’t use “Insert if New” personally).
  • Double-click on the “…” next to Append Field Map, select Custom Mapping, and map the fields between Alteryx and the database.
  • If it doesn’t work, double-click on the “…” under Key to Update and select your key ID.

Conclusion

I created this blog mostly for documentation for my current project, but it also counts towards my blog quota—two birds with one stone 🙂. Hope you found this useful!

Samuel Goodman
Author: Samuel Goodman