Workflow Query - SQL GUI - Bulk Insert: insert command field list

Workflows
, , ,
1

Two issues:

  1. Is it possible to specify a list of fields for the Insert command? Problem: In the Postgres SQL table, there is a computed field, but the system tries to insert a value into it, which leads to an error. Using an SQL query for insertion is not an option, as the table has more than 7000 records and it takes a very long time - the records have to be inserted one by one in a LOOP.

  2. Is there any way to do field mapping for insertion? I constantly have to write scripts if the field name just doesn't match!

2

Hey @peregrinus!

  1. You can specify whichever fields you want for Bulk Insert queries as long as you supply any required fields. In this example table, there are only 3 columns (id, name, age). Since id is auto-incrementing I don't need to supply it, and since neither name or age are required I can send either / or or both.

Screenshot 2023-12-19 at 9.52.01 AM
Screenshot 2023-12-19 at 9.52.01 AM1014×1348 65.7 KB

Obviously this is a hard coded example, but you can do the same with data coming from anywhere. It would seem that the data you are supplying there has some sort of reference to the computed column. You'd need to remove that reference from the data that is being inserted so that it isn't sent to the db.

  1. There isn't any native capability to changed column names automatically. Scripts are likely needed, but would love a little bit more detail on what you are doing, where the data is coming from, the mis-matches, etc.