DataWrap2ODBC Error - Mapped column not part of INSERT statement

unknown

I've set up a table in our Redshift database whose first 2 columns are keys and last 3 columns are data. I'm trying to populate the 3 data columns from Alteryx using an Output tool, expecting the 2 first columns to auto-populate

The DDL for the table is:

CREATE TABLE table_name
(
    key INTEGER IDENTITY PRIMARY KEY,
    parent_key INTEGER DEFAULT -1 REFERENCES table_name(key),
    file_name VARCHAR(48) NOT NULL,
    file_datetime TIMESTAMP NOT NULL,
    created_by VARCHAR(32)
);

The expectation is that there will be several cases where the record will have no parent_key, hence the DEFAULT -1.

The workflow I'm trying to run creates a stream with just the 3 columns file_name, file_datetime, and created_by with their data types set to be the same as what the database expects:

This is then pushed into an Output tool where the Output Options is Append Existing, and Append Field Map is set to map those 3 fields from the input stream to their corresponding Redshift columns:

However, despite mapping the columns explicitly, the Output tool is not including file_name in the INSERT statement and consequently returns the following error:

DataWrap2ODBC::SendBatch: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query:

[SQLState XX000] ERROR:  Cannot insert a NULL value into column file_name¶

DETAIL:  ¶

  -----------------------------------------------¶

  error:  Cannot insert a NULL value into column file_name¶

  code:      8007¶

  context:   query execution¶

  query:     18004363¶

  location:  column:3¶

  process:   query2_56_18004363 [pid=31420]¶

  -----------------------------------------------¶¶

Insert into "schema_name"."table_name"("file_datetime","created_by") Values (?,?)

Just to test it, I dropped the NOT NULL condition from the table and the upload then ran without an error, but no value was passed into file_name since the above query ran as-is

I also tried using the In-DB tools instead by using a Data Stream In to a temporary table and then Write Data In-DB using the Append Existing option, but that also failed because it tries to INSERT the temp table which is missing the key columns

How can I get the Output tool to include the file_name column in its INSERT query? All I want to run is:

INSERT INTO schema_name.table_name(file_name, file_datetime, created_by) VALUES (?, ?, ?)

danilang

hi @Inactive User 

Since the query ran correctly when you dropped the Not Null condition from the DB table, it looks like your workflow is trying to push NULL values in some of the file_name records.  Check the values going into the Output Data tool for NULL values in file_name

Dan