Forum Discussion

RStelzig's avatar
RStelzig
New Contributor
4 years ago

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

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

database

4 Replies

  • rriley99's avatar
    rriley99
    New Contributor III
    2 years ago

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

    • RStelzig's avatar
      RStelzig
      New Contributor
      2 years ago

      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's avatar
        rriley99
        New Contributor III
        2 years ago

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

  • alchemiz's avatar
    alchemiz
    Contributor III
    2 years ago

    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