Skip to main content
  • EN

Hi,

 

I have a dynamic workspace where I am reading in multiple csv update files for multiple feature types to upsert into our database. I had set the primary key dynamically dependent on the target table in the database. I cannot set a where clause on an UPSERT (which I do use with other feature types that have the ability to do I, U, or D) so went to use the "Row Selection: Columns:" section instead. This parameter does not work in the normal way with attributes. It presumes the selected attribute is the primary key field (no orange left arrow in front of the name), rather than using the value. I tried manually adding @Value(primary_key) (the attribute that stores the primary key column name) in the parameter but the feature writer also presumes it is literal and obviously can't find a column called "@Value(primary_key)" and errors.

 

This is something that I would like to do, and is very much a part of a dynamic workflow. Otherwise I will have to create one dynamic feature writer for each feature type so I can manually set the primary key column - negating the efficiency of using the dynamic workflow. I can see that FME 2023.1 works in the same way as 2022.2.0 (which is what I am working in at the moment.

 

Is this something that will come in in the future? Should I add it to the Ideas?

Hi @johnt​ I just tested dynamic writing + Upsert with the PostgreSQL Writer and was able to get it working correctly.

In the User Attributes tab of your Dynamic PostgreSQL Writer, you'll need to make sure that you've exposed the primary key column so you can use it in the Match Columns parameter.

image 

In my workflow I used the SchemaScanner to create the schema to be used in the writer mid-workspace. You can see how I configured the writer in the screenshot below. Because we're using the schema from the SchemaScanner, we need to set the Schema Source to "Schema From Schema Features" and SChema Definition Name must be set to fme_feature_type_name (this is the name of the corresponding schema feature created by the SchemaScanner). image 

I find if I'm having issues with the schema being picked up then the SchemaScanner is a good solution, but it isn't necessary to use if the schema coming from your Dynamic CSV Reader is working fine.

If you're interested in the SchemaScanner, you can find more info here: https://community.safe.com/s/article/How-to-SchemaScanner-Transformer

 

Hope this helps!

 

Reply


Community Stats

30,108
Posts
114,295
Replies
37,745
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.