Is it possible to perform an upsert to Postgres in SnapLogic?

[RStelzig]

Hi Everyone,

Is there an option to configure an existing Postgres snap to perform an upsert to a given table?

I am building a pipeline to create a daily feed to a postgres database and would like to capture records that have been modified or created in the source during the past day and then update existing records or insert a new record if they do not yet exist. Is this possible in SnapLogic?

I have used this functionality in other tools using an update activity with an option to insert if the record does not exist. I am newer to SnapLogic & haven’t yet come across this feature in the documentation so I wasn’t sure.
Thanks!!

[rriley99]

I have the same question, did you find a solution?

[RStelzig]

Hi @rriley99 ,

I did find a solution that I have been using for almost a year without error. It's similar to @alchemiz  suggestion below which I haven't tried yet but will definitely check out in the future.

I am using a slightly different approach on the scripting as I am using a staging table in my database.  In the Postgres - Execute Snap I am using the following:

Hope that is helpful to you!

[rriley99]

This was exactly what I was going to try when I got to that stage. I will write back once I get there!

[alchemiz]

Hi @RStelzig ,

Good day, you can create a sql script that will handle update and insert then set it in the PostgresSQL - Execute snap.

Warning this approach might be a little error-prone as it will try to concatenate a mix of data types e.g. field type is a String type where in the script you need to enclosed it with single quote e.g. 'this is a string value' 

Thanks,
EmEm