Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Duplicate key error with Table input

I have an Orchestration which creates this table:

CREATE TABLE public.mt_documents
(
activityid varchar(2000),
displayname varchar(2000),
endtime timestamp,
groupid varchar(2000),
starttime timestamp,
"user" varchar(2000),
devicename varchar(2000)
)

Now, I want to use this table as a table_input in a Transformation. Whenever I add anything to Column Names I get the error "duplicate key activityid". There certainly are duplicate values in activityid. How do I get past this error? Why are duplicate value being checked for?

6 Community Answers

Matillion Agent  

Kalyan Arangam —

Hi Quinn,

I am unable to reproduce this issue. I created your table and inserted duplicate values and still works fine.

Please check if you the DDL above is incomplete.

Best
Kalyan


Quinn Wildman —

I am quite certain my DDL is correct. I am using SQL Workbench/J to get the meta data.

I also created a table with a primary key and verified that SQL/Workbench/J reports the primary as such.


Matillion Agent  

Kalyan Arangam —

Hi Quinn,

I would recommend we start by replicating this in a separate table.
Please make a copy of your table with some duplicate rows and see if you get the same issue on the new table.

If you do, please email us the DDL for the table and sample data. Its important we replicate this at our end. Also send us your log file from the Admin menu.
Note that you may not be able to attach items to this call. Please email the requested items with subject referencing ticket 15370.

Best
Kalyan


Quinn Wildman —

Just getting back to this. I will do as you have requested tomorrow morning.


Quinn Wildman —

I believe I figured out how to reproduce the problem. Create another table like this:

CREATE TABLE public."mt-documents"
(
objid integer DEFAULT "identity"(357022, 0, '1,1'::text),
activityid varchar(2000),
displayname varchar(2000),
endtime timestamp,
groupid varchar(2000),
starttime timestamp,
"user" varchar(2000),
devicename varchar(2000)
);

The fact that there is a table with an understore (_) in the name and one with a dash (-) seems to be the problem. Also, the identity field in mt-documents is required. No data is required in either table.


Matillion Agent  

Paul Johnson —

Hi Quinn,

I have run the above DDL in an orchestration using the SQL component and then used that table in a table input (with no data). So i have both a mt-documents table and a mt_documents table. The table input component does not return any errors for me. Are these the steps you are following to reproduce the issue?

Can you confirm the version of Matillion you are running please.

Regards,
Paul

Post Your Community Answer

To add an answer please login