adplus-dvertising

PostgreSQL UPSERT

Asked 2 days ago
Viewed 8 times

I'm trying to perform an upsert in postgreSQL. Using this post as a reference Insert, on duplicate update in PostgreSQL?

I'm just not sure how to sanitize my data properly.

Currently I am trying

"BEGIN; INSERT INTO clients (id, name, phone, contact) VALUES ('{0}','{1}','{2}','{3}') ON CONFLICT (id) DO UPDATE SET name = '{1}', phone = '{2}', contact= '{3}';".format(data['id'], data['name'], data['phone'], data['contact']);

I believe there is an issue with my DO UPDATE SET command but I am not sure exactly what.

asked 2 days ago

Correct Answer

Your SQL command should work. It's unclear what the perceived issue should be? (I don't recognize the framework code around it, supplying parameters.)

One thing: it's typically better to use the excluded table:

INSERT INTO clients
       (id   , name, phone, contact)
VALUES ('{0}','{1}','{2}' ,'{3}')
ON     CONFLICT (id) DO UPDATE
SET    name    = EXCLUDED.name
     , phone   = EXCLUDED.phone
     , contact = EXCLUDED.contact
;

Related:

answered 2 days ago