Thread: pseudo-serial values in dual primary key?
Is it possible to have the equivalent of a serial data type in a table, sub-categorized? Assume the following: create table categories (id serial, title varchar); Now, I want to create an entries table, and by default, count serially by category, so that category 1 has entries.sequence of 1, 2, 3, and so does category 2. (where sequence= 1, 2, 3...) Something like: create table entries ( categories_id integer not null references categories(id), sequence default max(entries.sequence WHERE categories_id=this.categories_id), primary key (categories_id, sequence) ); I'm not sure about the semantics of this, but i want sequence to start at 1, and count up, for its category as defined by categories_id. I already know that I can set enforce the uniqueness of categories_id and sequence with thte primary key, and I could just write some more app code to do a query to get the max value of sequence where categories_id=$categories_id, but can this be done without adding a bunch of xtra application code? -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
Benjamin Smith <lists@benjamindsmith.com> writes: > Is it possible to have the equivalent of a serial data type in a table, > sub-categorized? > > Assume the following: > > create table categories (id serial, title varchar); > > Now, I want to create an entries table, and by default, count serially by > category, so that category 1 has entries.sequence of 1, 2, 3, and so does > category 2. (where sequence= 1, 2, 3...) Something like: You could make a trigger that set the column to the nextval of different sequences depending on the category. However you should realize that sequences can skip numbers. If you really need them to be sequential then you'll have to lock the table in your trigger and "select max(id)" for your category. This will be much much slower. -- greg
On Sat, Mar 19, 2005 at 01:43:07 -0800, Benjamin Smith <lists@benjamindsmith.com> wrote: > Is it possible to have the equivalent of a serial data type in a table, > sub-categorized? [snip] > Now, I want to create an entries table, and by default, count serially by > category, so that category 1 has entries.sequence of 1, 2, 3, and so does > category 2. (where sequence= 1, 2, 3...) Something like: [snip] > I'm not sure about the semantics of this, but i want sequence to start at 1, > and count up, for its category as defined by categories_id. I already know > that I can set enforce the uniqueness of categories_id and sequence with thte > primary key, and I could just write some more app code to do a query to get > the max value of sequence where categories_id=$categories_id, but can this be > done without adding a bunch of xtra application code? Serials should be used for obtaining unique values. Within a single session you can also depend on them to increase (unless you have set the serial to allow for wrap around). There can be gaps. Different sessions may get values out of order in time. If you aren't deleting records or updating the sequence values, then the simplest thing to is to lock the table, find the maximum value for a category (using order by and limit instead of the max aggregate if you have an index on category and the sequence) and use the next integer for the sequence value. However, it may be worth asking why you want to do this? Why not just use one sequence over the whole table. You can still use that for ordering within a category, and if necessary the application can renumber when outputing data.