Using Bulk Upsert with multiple new records

[troot]

I have an employee table (id, fname, lname etc.) and a skills table (id, skill_name). They are linked by way of a junction table emp_skillz (id, emp_id, skill_id).

I have a radio button group in a form that renders for all the skills in the skills table.

I'm using a JS query to create a data array (from the selected buttons in the radio button group) to feed to a Bulk Upsert with Primary Key (GUI).

When I access a employee record that hasn't had any skills assigned to it, I select a couple of buttons and press "Submit", I get the following error.

Primary key id is not unique in the supplied array. The following keys were duplicated: null -- (2 dupes)

All thoughts welcome.

[mark]

Hi there Tom,

Would you mind sharing a screenshot of how your queries are currently configured?

[troot]

This one triggers the other queries.

This one deletes the current skills

This one builds the array of skills to be upserted

Here's what it looks like when I run it by itself.

This one does the upsert via primary key

The Error about duplicate null values comes from this query. It's true...there are duplicate NULL values but isn't that the purpose of the upsert?

[mark]

Thank you for the additional details. Can you try using undefined instead of null?

[troot]

Same error.

Primary key id is not unique in the supplied array. The following keys were duplicated: undefined -- (2 dupes)

[mark]

What if you just don’t include that key at all if it is undefined/null?

[troot]

id: (qry_Skillz_By_Employee.data.es_id[index]?qry_Skillz_By_Employee.data.es_id[index]:), is a syntax error

id: (qry_Skillz_By_Employee.data.es_id[index]?qry_Skillz_By_Employee.data.es_id[index]:''), yields Primary key id is not unique in the supplied array. The following keys were duplicated: -- (2 dupes)

id: (qry_Skillz_By_Employee.data.es_id[index]?qry_Skillz_By_Employee.data.es_id[index]:""), yields
Primary key id is not unique in the supplied array. The following keys were duplicated: -- (2 dupes)

Is there another way to not include them?

[troot]

When I try to place the "id: " part in the if/then construct, I get syntax errors again. It doesn't like that the line doesn't start with xxxx:

like this...

(qry_Skillz_By_Employee.data.es_id[index]?"id:"+qry_Skillz_By_Employee.data.es_id[index]:undefined),

[mark]

A quick and dirty solution would be setting the key to undefined like you were before, then looping over the array and running JSON.parse(JSON.stringify(item)) on each item in the array.

[troot]

I'm not familiar enough with JSON to know what that would do? It appears to force the values into their string equivalents. How does this resolve the duplicate Primary Keys problem?

Thanks for your attention.

[mark]

Due to the way that those functions work, when you stringify the object then parse it back, any keys whose value is “undefined” will be removed.

[troot]

well...excluding the id: when there is no value doesn't seem to work either.

With 2 existing skills and adding a 3rd skill...

// jsMakeBulkBundleArray
  var newData = [];
  select_Skillz.value.forEach((skill, index) => {
      newData.push({
      	emp_id: tbl_list_all_employees.selectedRow.data.emp_id,
      	skillz_id: skill,
        ...(qry_Employee_Skillz.data.es_id[index] && {id: qry_Employee_Skillz.data.es_id[index]})
		});
});
return newData

Creates...

0 [emp_id:1, skillz_id:59, id:14]
1 [ emp_id:1, skillz_id:70, id:15]
2 [emp_id:1, skillz_id:82]

Now the upsert query complains...

Primary key id is not present in the supplied array

[troot]

At this point, I have to ask how the “Upsert with Primary Key” is supposed to work?

[mark]

Hey Tom,

It turns out that there may be a bug with the way our DB connector is processing these upserts Can you do a quick test to see if the array contains only new rows (ie none have the id field) if the behavior is any different?

[troot]

I ran the process with only new records to be added (no record id (key)).

And got the following message.

Primary key id is not unique in the supplied array. The following keys were duplicated: undefined -- (2 dupes)

[mark]

Hmm, yeah, this definitely looks like a bug. I assume that this ID column is an autoincrementing value, so you are unable to calculate and insert the correct id values

?

[tommyk421]

I'm seeing the same behaviour when trying to use Bulk Upsert via Primary key and adding new records from a CSV parser - no primary key.

Is this still a bug?

[byron]

same issue here. normally if inserting into a database, we could have multiple NULL values in the primary key, and just let them autoincrement. So I think the simple solution here would be if retool could allow the query to run with multiple NULL values, instead of considering that an error @mark . Thank you!

[Korbinian123]

Hi,

same issue here, trying to read a csv and insert the records into a postgres table.
@mark any update if this is still a bug?

Thanks,

Korbinian

[Piotr]

Same here... any news on that?