Update SQL database registers based on JSON

Question

I have a table with 30k clients, with the ClientID as primary key. I'm getting data from API calls and inserting them into the table using python.

I'd like to find a way to insert rows with new clients and, if the ClientID that comes with the API call already exists in the table, update the existing register with the updated information of this client.

Thanks!!

`INSERT INTO ...VALUES ... ON CONFLICT (ClientID) DO UPDATE` — Slava Rozhnev, Mar 15 '21 at 19:29
Thank you very much! Is it possible to use it like the following? INSERT INTO ...VALUES ... ON CONFLICT (ClientID) DO UPDATE SET (columns)=(values) — Sawaya, Mar 15 '21 at 19:57
No. You need to adjust this construction according your table structure. — Slava Rozhnev, Mar 15 '21 at 20:18

score 0 · Accepted Answer · answered Mar 17 '21 at 13:58

A snippet of code would be nice to show us what exactly you are doing right now. I presume you are using an ORM like SqlAlchemy? If so, then you are looking at doing an UPSERT type of an operation.

That is already answered HERE

Alternatively, if you are executing raw queries without an ORM then you could write a custom procedure and pass required parameters. HERE is a good write up on how that is done in MSSQL under high concurrency. You could use this as a starting point for understanding and then re-write it for PostgreSQL.

Update SQL database registers based on JSON

1 Answers1