Amazon Redshift - Error: Output Data Primary Key required for Update option

[ntobon]

Issue
 

When updating an Amazon Redshift table receive error 'Output Data (n) Primary Key required for Update option' even though the table has a Primary Key.



 

Environment
 

• Alteryx Designer
  • All Versions

Diagnosis
 

Check the case of the table name. For example, in a database client (RazorSQL) the name of the table is hh_co_store_file. Visual Query Builder also shows the name in lower cases:





We ran a query outside Alteryx and confirmed that the the table has a Primary Key:

Cause
 

The string in the Output Data Tool configuration has the name of the table in upper cases:

Amazon Redshift is not case sensitive, and all names default to lowercase. Amazon Redshift objects include tables, views, and columns. So, Redshift converted the name to lower cases.
 

Solution


You should always use lowercase with Redshift for everything.  Modify the string and change the table name to lower cases.