0

I would like to have an object_id column in an objects postgres table, but where each object_id is scoped to org_id and type_id. That is, each object_id is unique to each (org_id, type_id), so object_id is not unique in regards to the whole table but only in relation to those two properties.

Is that possible in postgres? If so, what do you need to do? If not, how should I handle "incrementing" this object_id in relation to the two scoping properties so that it is generally efficient during record creation? What is the standard approach? Note, there is no id on this table, the "id" is technically the tuple (org_id, type_id, and object_id), that triple-combo is what is "unique" globally.

I would like object_id to just be bigserial, but not sure that is possible.

Lance
  • 75,200
  • 93
  • 289
  • 503
  • 2
    "What is the standard approach?" -- Not to do it. Maybe [edit] the question and explain (in greater detail) what you are *actually* trying to model to get some suggestions on that. Could be an [XY problem](https://en.wikipedia.org/wiki/XY_problem). – sticky bit Jan 15 '22 at 05:14
  • Also https://stackoverflow.com/questions/24918552/serial-numbers-per-group-of-rows-for-compound-key – Bergi Jan 15 '22 at 11:31

0 Answers0