The Many Faces of Distinct in PostgreSQL

Re:The Many Faces of Distinct in PostgreSQL by paulddraper on Monday May 22, 2023 @02:10PM (#36034668)

So....what is gh_id and gh_node_id?

Like, you have

    gh_id | gh_node_id
    ------------------
      1   |     2    
      2   |     1

And then you want to "upsert" (1, 1).

You want both records to be updated?

I'm still struggling to see why you would want to do a upsert relative to multiple unique constraints at once.

[ Parent | on HN ]

Re:The Many Faces of Distinct in PostgreSQL by cropcirclbureau on Monday May 22, 2023 @08:56PM (#36038565)

Ah, I'd run into the lack of multi constraint ON CONFLICT in postgres yesterday and was wondering about the omission. I'd not considered this case and GP's code smell comment makes sense now.

In my specific case, business logic dictates uniqueness of both columns is tied together i.e. if an incoming tuple has a value (1, 2) today, all subsequent expected tuples with `gh_id` 1 will also have `gh_node_id` of 2. What's the best practice to model this constraint?

[ Parent | on HN ]

Re:The Many Faces of Distinct in PostgreSQL by paulddraper on Monday May 22, 2023 @10:12PM (#36038987)

Then upsert based on gh_id.

I'm not saying that multiple unique identifiers is a code smell; I am claiming that an upsert is only sensibly done in the context of one constraint.

[ Parent | on HN ]