4

In a Rails app I'm building, I need what I can best describe as a contextual auto-incrementing column. Essentially, the Bar model belongs to Foo via a foo_id column. I need to increment contextual_id on Bar rows in the context of its Foo, meaning that for Foo foo_1, its first Bar has a contextual_id of 1, the second has a contextual_id of 2, and for Foo foo_2, its first Bar also has a contextual_id of 1. Hope this makes sense.

Obviously, this should be handled at the database level, but I'm not sure how to do that in Postgres. Any help would be greatly appreciated.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
clem
  • 3,524
  • 3
  • 25
  • 41
  • I suppose you could use a function to choose from various sequences (and a trigger to create the sequences as needed). But I'm curious why you need these values? You should generally consider `id`s to be completely opaque apart from per-table uniqueness. – mu is too short Sep 02 '13 at 03:11
  • 1
    @mu-is-too-short They still have normal serial primary keys, the contextual ID is to aid in implementing a specific feature. – clem Sep 02 '13 at 06:31
  • Do you really need to store them? Could you get away with a `row_number()` window function instead? How comfortable are you with poking AR in the eye and using triggers and database-side functions? – mu is too short Sep 02 '13 at 17:54
  • This is related and something I've used successfully http://stackoverflow.com/questions/10210898/what-is-the-proper-way-to-manually-sequence-a-column-in-postgres – toxaq Jan 12 '14 at 05:11

0 Answers0