Update & Insert data in PostgreSQL

[ieassaad]

Hello All,

• Describe the issue/error/question

First of all, I am new to this platform. So, I am doing a Schedule Trigger with HTTP Request to get data and I want to update all items in the table and insert a new data in PostgreSQL if there is any.
Also, I was trying to do looping by using SplitInBatches & IF nodes to help me updating and inserting the data changes in my table.

• Please share the workflow

• n8n version:
  Version 0.206.1
• Database you’re using (default: SQLite):
  API to PostgreSQL

[MutedJam]

Hi @ieassaad, welcome to the community

So you’re looking for an upsert operation? Perhaps you might find it easier to use the Execute Query operation rather than have separate Update and Insert nodes, something like this:

Before:

After:

So you can see how in this example the existing datasets with id 1 and 2 are updated, whereas the new dataset with id 3 is newly inserted using a query like this:

INSERT INTO users (
	id,
	email
) VALUES (
	$1,
	$2
)
ON CONFLICT (id)
DO UPDATE SET email = $2;

The parameters would be id and email:

You could also add RETURNING *; after the query to see the newly inserted rows.

Check out https://www.delftstack.com/howto/postgres/postgresql-insert-or-update-if-exists/ for more details on how to use such upsert queries.

[ieassaad]

Thank you for your help