cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

RStelzig
New Contributor

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!!

4 REPLIES 4

rriley99
New Contributor III

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

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:

RStelzig_0-1699383551313.png

Hope that is helpful to you!

rriley99
New Contributor III

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

alchemiz
Contributor III

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' 

alchemiz_0-1699147837513.png


Thanks,
EmEm