Writing/Updating to Panoply from Alteryx

Hello all,

I am new to using Panoply (though not databases) and am having some issues using Panoply with Alteryx. I use the postgres driver to write a new table from Alteryx to Panoply, with one field as “id”, which Panoply often automatically picks up as a Primary Key. However, when I go to update this table using the same workflow in Alteryx (having changed the config to Update; insert if new), the driver reports that a Primary Key is required for updating. My colleague told me this was an issue with file uploads, but that is quite different from using a data connection.

Has anyone seen anything like this before?

Thanks!

Thanks for the post Louisa.

An update to anyone reading this:
Louisa managed to make Alteryx write and update data in Panoply by simplifying the update process that Alteryx needed to perform in Panoply.

Louisa, it would be great if you could add a general explanation on the solution.

Thanks!
Gal

Thanks Gal,

We got to the bottom of this by identifying two things about the underlying architecture of Panoply, which relates to Redshift tables.

  1. Primary Keys are not the same kind of constraint as they are in other SQL deployments, in that they are informational rather than enforced as constraints.
  2. Redshift doesn’t support “Upsert” operations, wherein an update and an insert command are merged within the same query. The “Update; Insert if New”) output option.

This means that Alteryx’s output connection set up like this will error. You can work around this in Alteryx Designer instead in a number of ways, e.g. a pattern involving a join and transpose tool to compare fields which have been edited in the workflow, which rows they belong to, and only updating those rows, adding new rows in a separate branch of the workflow. It essentially means that the work of enforcing the constraint of a primary key must be shifted to your workflow instead of the database itself.

1 Like