Updating a PostgreSQL Database Using UPSERT

Introduction

In FME 2022.0, we have introduced the ability UPSERT updates to PostgreSQL and PostGIS databases. UPSERT is the ability to add a new row if it doesn’t already exist, but also update the row if it does exist in a single action. In order to use UPSERT, your database will need a unique ID. In this tutorial, we will read in an existing PostgreSQL table, add a new row as well as modify a couple of other rows, then write it back to the same database table using UPSERT. 
 

Step-by-step Instructions

Part 1: Seed Data

Before you begin, you will need to seed the data into your personal PostgreSQL database or one that you’ve created following Creating a PostgreSQL/PostGIS Training Database with FME.   

1. Add a CSV Reader
Open a blank workspace in FME workbench and add a CSV reader to the canvas. Browse to the Original.csv dataset available for download from the Files section of this article. Click OK to add the reader. 


2. Write to PostgreSQL 
Add a PostgreSQL writer to the canvas and connect to the FMETraining database that was set up in Creating a PostgreSQL/PostGIS Training Database with FME. Set the Table Definition to Automatic and click OK. 


In the Feature Type dialog, set the Table Name to CellSignals, and the Table Qualifier to public. We can leave the Table Handling to Create If Needed since there currently isn’t a CellSignals table in the FMETraining database.   


Click OK, then connect the public.CellSignals writer feature type to the CSV reader feature type. 

Reopen the public.CellSignals writer feature type parameters, and switch to the User Attribute tab. Change the Attribute Definition to Manual. Then for the featureid attribute click the drop-down for Index and select Primary Key. In order to UPSERT a primary key needs to be identified. 


3. Run the Workspace
Run the workspace, this will create the CellSignals table in the training database, which will give us a blank slate to demonstrate UPSERTs. 

Disable the CSV reader and the PostgreSQL writer as we no longer need them. If you need to reset the table, you can rerun this part of the workspace. Before rerunning this section, change the Table Handling to Drop and Create. 
 

Part 2: UPSERT into PostgreSQL

1. Add a PostgreSQL Reader
Continuing in the same workspace as Part 1, but in a blank section, add a PostgreSQL reader to the canvas and connect to the training database (or your own database where you seeded the data).  


In the parameters, select the public.CellSignals table, then click OK three times to finish adding the reader. 


2. Add the Updated CSV
Next, we need to read the CSV containing the updates. Add another CSV reader to the canvas and browse to the Updates.csv dataset. 


In the parameters, change the Feature Type Name(s) to From File Name(s), then click OK twice to finish adding the reader. 


3. Detect Changes
Using the ChangeDetector transformer, we can detect any changes to the original dataset (public.CellSignals) and then assign a database operation to control how the data gets written. 
Add a ChangeDetector to the canvas and connect the Original input port to the public.CellSignals reader feature type, then connect the Revised input port to the Updated CSV reader feature type. 



In the parameters, set the Update Detection Key Attributes to featureid. Next, set Attribute Matching Strategy to Match Selected Attributes then for Selected Attributes select:

• code
• featureid
• num_measures
• power
• quality
• recorded_tstamp
• stationid

Finally, disable Check Geometry, then click OK. 

4. Set UPSERT
Now that we have identified which attributes have been updated or inserted, we need to tell FME to set the fme_db_operation to UPSERT. Add an AttributeCreator to the canvas and connect it to both the Inserted and Updated output ports on the ChangeDetector. 


In the parameters, create a new attribute called fme_db_operation and give it the value of UPSERT. So that we can see exactly which records we’ve updated or inserted, let’s give the attribute num_measures a value of 999. 


5. Write to PostgreSQL
Add another PostgreSQL writer to the canvas and connect to the FMETraining database. Set the Table Definition to Copy from Reader and then click OK.


In the Select Feature Type dialog, select the public.CellSignals reader. 


Connect the writer feature type to the AttributeCreator and the Deleted output port on the Change Detector. 


Next, in the PostgreSQL writer parameters, set the Feature Operation to fme_db_operation, then set the Table Handling to Use Existing. Now for Row Selection, select featureid for Match Columns. This attribute needs to be unique for each record. Then click OK. 


Note: If you are writing to an existing table confirm that the Match Column attribute is the primary key. 

6. Run the Workspace and Inspect the Output
Now run the workspace, and then inspect the output in Visual Preview. If you sort the num_measures attribute by descending, you’ll see all of the attributes that were UPSERTed, as indicated by the value of 999. 

 

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.