Re: pseudo-serial values in dual primary key?

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