Postgres update/add

Hi

I have one table in posgres and i have a number of items. I would then check if that item exists in the database if it is not existing not it should be added. How could this be done? By query and then an if formula?

= lookup item, if existing = stop
= lookup item, if no existing = add

I have tried a few different ones but not really got it to work.

Kind regards
Mattias

I guess the problem you have is that the Postgres-Node does not output anything in case it does not find the item. So you have to set on the Postgres-Node under Node (not under Parameters!) the option “Always Output Data” to “true”. If you do not do that it will not output anything and the workflow will stop. Only with that option will it at least output an empty item and you can then check for it.

I created the following example workflow which assumes you have a table called “product” which has the properties: id, timestamp

{
  "nodes": [
    {
      "parameters": {
        "values": {
          "number": [
            {
              "name": "id",
              "value": 3912
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        450,
        600
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{!!$node[\"Postgres\"].json[\"id\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        850,
        600
      ]
    },
    {
      "parameters": {
        "values": {
          "number": [
            {
              "name": "id",
              "value": "={{$node[\"Set\"].json[\"id\"]}}"
            }
          ],
          "string": [
            {
              "name": "timestamp",
              "value": "={{ new Date().toISOString() }}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1050,
        700
      ]
    },
    {
      "parameters": {},
      "name": "End",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        1050,
        500
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT * FROM product WHERE id={{$node[\"Set\"].json[\"id\"]}}"
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        650,
        600
      ],
      "alwaysOutputData": true,
      "credentials": {
        "postgres": ""
      }
    },
    {
      "parameters": {
        "table": "product",
        "columns": "id,timestamp"
      },
      "name": "Postgres2",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        1250,
        700
      ],
      "credentials": {
        "postgres": ""
      }
    }
  ],
  "connections": {
    "Set": {
      "main": [
        [
          {
            "node": "Postgres",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "End",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set1": {
      "main": [
        [
          {
            "node": "Postgres2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Apparently I am still struggling, now the set node is only taking the first item from my array, I get really confused, but probably it is on my side:)

{
  "nodes": [
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "id",
              "value": "={{$node[\"convertSubscribers\"].json[\"id\"]}}"
            },
            {
              "name": "application",
              "value": "={{$node[\"convertSubscribers\"].json[\"application\"]}}"
            },
            {
              "name": "discipline",
              "value": "={{$node[\"convertSubscribers\"].json[\"discipline\"]}}"
            },
            {
              "name": "category",
              "value": "={{$node[\"convertSubscribers\"].json[\"category\"]}}"
            },
            {
              "name": "titles",
              "value": "={{$node[\"convertSubscribers\"].json[\"titles\"]}}"
            },
            {
              "name": "flagg_all",
              "value": "={{$node[\"convertSubscribers\"].json[\"flagg_all\"]}}"
            },
            {
              "name": "status",
              "value": "={{$node[\"convertSubscribers\"].json[\"status\"]}}"
            },
            {
              "name": "mcapplicationkey",
              "value": "={{$node[\"convertSubscribers\"].json[\"mcapplicationkey\"]}}"
            },
            {
              "name": "subscribe_localid",
              "value": "={{$node[\"convertSubscribers\"].json[\"subscribe_localid\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1380,
        450
      ]
    }
  ],
  "connections": {}
}

Not sure what you want to do exactly but looks generally correct to me. Without knowing what the node “convertSubscribers” does actually output and the same what the input of the “Set” nodes does output it is impossible to say for sure what is going wrong. Additionally would also have to know what you expect the “Set” node to do.

In the convertSubribers do i make an array become repeating items. Then i use the postgres to check if the item already exists. If yes then it should update (true on if) and if not exist it should add. The set node i used to prepare the json that should to into postgres. I just copied the example you gave me. The problem i have is is that it only add the first item of the array and to not repeat for all of them.

Honestly still do not understand what exactly you are doing. I really need an example workflow I can run to be able to help you.

Sorry did not see that all nodes was not added, that explain alot:) Sorry for that, in chrome on mac it have been abit tricky to copy nodes lately.

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "const item= [\n{\n  json: {\n  \"body\":{\n  \"id\": \"d2f83222\",\n  \"locationtype\": \"Vessel\",\n  \"locationname\": \"sdsf\",\n  \"mclocationkey\": \"trest\",\n  \"keydate\": \"\",\n  \"status\": \"Active\",\n  \"subscribewebhooks\": [\n    {\n      \"id\": \"e645c0bc\",\n      \"appid\": \"df878971\",\n      \"appname\": \"Barrier\",\n      \"mckey\": \"07425e38-e1a7-4377-a7cd-54c554b696\",\n      \"webhookprud\": \"http://checklist:5678/webhook/7/webhook/G1Mg_yS6AmGkfNJWsG_p6aWDHpJgwHy6ka\",\n      \"webhooktest\": \"http://checklist:5678/webhook-test/7/webhook/G1Mg_yS6AmGkfNJWsG_p6aWDHpJgwHy6ka\",\n      \"server\": \"d2f83222\"\n    }\n  ],\n  \"mcsubscribers\": [\n    {\n      \"id\": \"3F26C2D2-294F-4DEA-81AE-E462A66B8998\",\n      \"locations\": \"\",\n      \"application\": \"df878971\",\n      \"discipline\": \"machinery\",\n      \"category\": \"operation_planning\",\n      \"titles\": \"\",\n      \"flagg_all\": \"All\",\n      \"mcapplicationkey\": \"D8EA2A88-8A40-402D-ADEF-A3F2910AC38F\",\n      \"urltest\": \"http://n8n:5678/webhook-test/97/webhook/G1Mg_yS6AmGkfNJWsG_p6aWDHpJgwHy6ka\",\n      \"subscribe_localid\": \"DNVGL/Barrier/machinery/operation_planning/ALL\",\n      \"disciplineCategory\": \"machinery/operation_planning\"\n    },\n    {\n      \"id\": \"642b1127\",\n      \"locations\": \"\",\n      \"application\": \"4e797ba2\",\n      \"discipline\": \"operation\",\n      \"category\": \"voyagedata_update\",\n      \"titles\": \"\",\n      \"flagg_all\": \"All\",\n      \"mcapplicationkey\": \"f9168941-a74a-4b91-817e-a87e529d02\",\n      \"urltest\": \"http://n8n:5678/webhook-test/90/webhook/rec2GCEIk9vhAjYQI\",\n      \"subscribe_localid\": \"\",\n      \"disciplineCategory\": \"operation/voyagedata_update\"\n    },\n    {\n      \"id\": \"6bc31894\",\n      \"locations\": \"\",\n      \"application\": \"05e2dc76\",\n      \"discipline\": \"operation\",\n      \"category\": \"voyagedata_update\",\n      \"titles\": \"\",\n      \"flagg_all\": \"All\",\n      \"mcapplicationkey\": \"50502496-966a-4fd6-82ab-6023ec9f6b\",\n      \"subscribe_localid\": \"\",\n      \"disciplineCategory\": \"operation/voyagedata_update\"\n    }\n  ]\n  }\n}\n}\n];\n\nreturn item;\n"
      },
      "name": "Webhook",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        -520,
        600
      ]
    },
    {
      "parameters": {
        "functionCode": "const returnItems = [];\n\nfor (const subscribers of $node[\"Webhook\"].json[\"body\"][\"mcsubscribers\"]) {  \n    returnItems.push({json: {\n      id: subscribers.id,\n      application: subscribers.application,\n      discipline: subscribers.discipline,\n      category: subscribers.category,\n      titles: subscribers.titles,\n      flagg_all: subscribers.flagg_all,\n      mcapplicationkey: subscribers.mcapplicationkey,\n      status: subscribers.status,\n      subscribe_localid: subscribers.subscribe_localid,\n\n    }});\n}\n\nreturn returnItems;"
      },
      "name": "convertSubscribers",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        -330,
        600
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT* FROM mcsubscribers\nWHERE\nid  = '{{$item(0).$node[\"convertSubscribers\"].json[\"id\"]}}'"
      },
      "name": "getSubscribers",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        -110,
        600
      ],
      "alwaysOutputData": true,
      "credentials": {
        "postgres": "remotelocation"
      }
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{!!$item(0).$node[\"getSubscribers\"].json[\"id\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF1",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        80,
        590
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "id",
              "value": "={{$node[\"convertSubscribers\"].json[\"id\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        270,
        690
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "id",
              "value": "={{$node[\"convertSubscribers\"].json[\"id\"]}}"
            },
            {
              "name": "application",
              "value": "={{$node[\"convertSubscribers\"].json[\"application\"]}}"
            },
            {
              "name": "discipline",
              "value": "={{$node[\"convertSubscribers\"].json[\"discipline\"]}}"
            },
            {
              "name": "category",
              "value": "={{$node[\"convertSubscribers\"].json[\"category\"]}}"
            },
            {
              "name": "titles",
              "value": "={{$node[\"convertSubscribers\"].json[\"titles\"]}}"
            },
            {
              "name": "flagg_all",
              "value": "={{$node[\"convertSubscribers\"].json[\"flagg_all\"]}}"
            },
            {
              "name": "status",
              "value": "={{$node[\"convertSubscribers\"].json[\"status\"]}}"
            },
            {
              "name": "mcapplicationkey",
              "value": "={{$node[\"convertSubscribers\"].json[\"mcapplicationkey\"]}}"
            },
            {
              "name": "subscribe_localid",
              "value": "={{$node[\"convertSubscribers\"].json[\"subscribe_localid\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set2",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        280,
        480
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "table": "mcsubscribers",
        "columns": "id,application,discipline,category,titles,flagg_all,mcapplicationkey,status,subscribe_localid"
      },
      "name": "subscriberUpdate",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        450,
        450
      ],
      "credentials": {
        "postgres": "remotelocation"
      }
    },
    {
      "parameters": {
        "table": "mcsubscribers",
        "columns": "id,application,discipline,category,titles,flagg_all,mcapplicationkey,status,subscribe_localid"
      },
      "name": "subscribersAdd",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        500,
        670
      ],
      "credentials": {
        "postgres": "remotelocation"
      }
    }
  ],
  "connections": {
    "Webhook": {
      "main": [
        [
          {
            "node": "convertSubscribers",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "convertSubscribers": {
      "main": [
        [
          {
            "node": "getSubscribers",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "getSubscribers": {
      "main": [
        [
          {
            "node": "IF1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF1": {
      "main": [
        [
          {
            "node": "Set2",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "subscribersAdd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set2": {
      "main": [
        [
          {
            "node": "subscriberUpdate",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Because of the way the Postgres node currently operates there is right now sadly no simple way to build such a workflow. At least not with my very limited postgres query building skills. For that reason did I have to use SplitInBatches Node.

Here an example workflow which creates n8n workflows directly in the database and so does something similar like you want to do:

{
  "nodes": [
    {
      "parameters": {
        "table": "workflow_entity",
        "columns": "id,name,active,nodes,connections,settings,createdAt,updatedAt"
      },
      "name": "Postgres4",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        450,
        500
      ],
      "credentials": {
        "postgres": ""
      }
    },
    {
      "parameters": {
        "values": {
          "boolean": [
            {
              "name": "active"
            }
          ],
          "string": [
            {
              "name": "nodes",
              "value": "[]"
            },
            {
              "name": "connections",
              "value": "{}"
            },
            {
              "name": "settings",
              "value": "{}"
            },
            {
              "name": "createdAt",
              "value": "={{new Date().toISOString()}}"
            },
            {
              "name": "updatedAt",
              "value": "={{new Date().toISOString()}}"
            }
          ],
          "number": []
        },
        "options": {}
      },
      "name": "Set Defaults",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        250,
        500
      ]
    },
    {
      "parameters": {},
      "name": "End",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        1050,
        700
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$node[\"SplitInBatches\"].context[\"noItemsLeft\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        850,
        700
      ]
    },
    {
      "parameters": {},
      "name": "NoOp",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        650,
        400
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "table": "workflow_entity",
        "columns": "id,name"
      },
      "name": "Postgres5",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        250,
        300
      ],
      "credentials": {
        "postgres": ""
      }
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$json[\"exists\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF Exists",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        50,
        400
      ]
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        -150,
        400
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=select exists(select 1 from workflow_entity where id={{$node[\"SplitInBatches\"].json[\"id\"]}})"
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        -350,
        500
      ],
      "credentials": {
        "postgres": "local postgres"
      }
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        -550,
        400
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      id: 13,\n      name: 'test workflow 1'\n    }\n  },\n  {\n    json: {\n      id: 14,\n      name: 'test workflow 2'\n    }\n  }\n]"
      },
      "name": "Input Data1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        -750,
        400
      ]
    }
  ],
  "connections": {
    "Postgres4": {
      "main": [
        [
          {
            "node": "NoOp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Defaults": {
      "main": [
        [
          {
            "node": "Postgres4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "End",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "NoOp": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres5": {
      "main": [
        [
          {
            "node": "NoOp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF Exists": {
      "main": [
        [
          {
            "node": "Postgres5",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Set Defaults",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "IF Exists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "Postgres",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Input Data1": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Thanks! I will try, then we know at leat it was not only me. Thanks for the help