Hi everyone, I am trying to use meltano to load a table from tap-s3-csv with no key_properties into target-redshift. The problem is that Meltano will automatically create a table with primary key in Redshift, then trying to load a NULL value into the primary key column causing an error. Is there any way to resolve this problem?

@martin_leung are you using the primary_key_required setting? I wonder if setting that to false would solve your problem

hi @pat_nadolny the primary_key_required setting is set to false, despite that the target automatically created a primary key

A couple questions and debugging ideas: 1. Can you share more of the error traceback? 2. Did the table exist already before you ran the sync or did the target create the table for you? 3. Can you try running a SHOW query in redshift to see what the table definition is? Can you confirm that it has a PK defined on the table? 4. Can you trying running your tap into a file and inspecting the schema messages?

meltano invoke tap-s3-csv > output.json

. I see the logic in the target to not include primary keys in the create table statement if they arent present in the stream schema, so I wouldnt expect to see a PK defined on the table if the stream SCHEMA message doesnt contain one.

1. Here is more of the error traceback 2. The table did not exist before I ran the sync, the target created the table for me 3. CREATE TABLE meltano.paysimple_data ( account name character varying(10000) ENCODE lzo, account owner character varying(10000) ENCODE lzo, application monthly cc volume character varying(10000) ENCODE lzo, board date character varying(10000) ENCODE lzo, cc processor character varying(10000) ENCODE lzo, cc volume mtd character varying(10000) ENCODE lzo, ec crm id c character varying(10000) NOT NULL ENCODE lzo distkey, eclipsed 100 dollars cumulative cc date character varying(10000) ENCODE lzo, external id character varying(10000) ENCODE lzo, follow up date character varying(10000) ENCODE lzo, lead create date character varying(10000) ENCODE lzo, owner email character varying(10000) ENCODE lzo, partner character varying(10000) ENCODE lzo, partner rep character varying(10000) ENCODE lzo, sales notes character varying(10000) ENCODE lzo, stage character varying(10000) ENCODE lzo, status 1 character varying(10000) ENCODE lzo, status 2 character varying(10000) ENCODE lzo, status 3 character varying(10000) ENCODE lzo, total cc volume character varying(10000) ENCODE lzo, _sdc_extra character varying(65535) ENCODE lzo, _sdc_source_bucket character varying(10000) ENCODE lzo, _sdc_source_file character varying(10000) ENCODE lzo, _sdc_source_lineno numeric(18,0) ENCODE az64, PRIMARY KEY ("ec crm id c") ) DISTSTYLE AUTO SORTKEY ( account name ); 4. the logs in output.json do not seem to show a primary key {_"type"_: "SCHEMA", _"stream"_: "paysimple_data", _"schema"_: {_"type"_: "object", _"properties"_: {_"Ec Crm ID C"_: {_"type"_: ["null", "string"]}, _"Partner"_: {_"type"_: ["null", "string"]}, _"External ID"_: {_"type"_: ["null", "string"]}, _"Account Owner"_: {_"type"_: ["null", "string"]}, _"Account Name"_: {_"type"_: ["null", "string"]}, _"Partner Rep"_: {_"type"_: ["null", "string"]}, _"CC Processor"_: {_"type"_: ["null", "string"]}, _"Owner Email"_: {_"type"_: ["null", "string"]}, _"Follow Up Date"_: {_"type"_: ["null", "string"]}, _"Lead Create Date"_: {_"type"_: ["null", "string"]}, _"Board Date"_: {_"type"_: ["null", "string"]}, _"Stage"_: {_"type"_: ["null", "string"]}, _"Status 1"_: {_"type"_: ["null", "string"]}, _"Status 2"_: {_"type"_: ["null", "string"]}, _"Status 3"_: {_"type"_: ["null", "string"]}, _"Application Monthly CC Volume"_: {_"type"_: ["null", "string"]}, _"Sales Notes"_: {_"type"_: ["null", "string"]}, _"Eclipsed 100 Dollars Cumulative Cc Date"_: {_"type"_: ["null", "string"]}, _"CC Volume MTD"_: {_"type"_: ["null", "string"]}, _"Total CC Volume"_: {_"type"_: ["null", "string"]}, _"_sdc_source_bucket"_: {_"type"_: "string"}, _"_sdc_source_file"_: {_"type"_: "string"}, _"_sdc_source_lineno"_: {_"type"_: "integer"}, _"_sdc_extra"_: {_"type"_: "array", _"items"_: {_"type"_: "string"}}}}, _"key_properties"_: []}