Is there another way to do an UPSERT in Postgres with multiple ON CONFLICT constraints?

I have a profiles table with two columns email and phone and both are optional.

I want to allow API users to supply email or phone or both. However, I want to be able to do an upsert with ON CONFLICT but the problem is that ON CONFLICT allows you to use only one constraint.

In MySQL, I believe you can do something like:

ON DUPLICATE KEY UPDATE email=values.email, phone=phone.email

But that doesn't seem possible with Postgres, because you have to supply a constraint or a column:

ON CONFLICT ON CONSTRAINT (profiles_email_key) DO UPDATE SET email=excluded.email, phone=excluded.phone

Is there an alternative solution for me here?

https://stackoverflow.com/questions/65571913/is-there-another-way-to-do-an-upsert-in-postgres-with-multiple-on-conflict-const January 05, 2021 at 09:08AM