Re: pseudo-serial values in dual primary key?

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: pseudo-serial values in dual primary key?
Date: 2005-03-19 17:03:14
Message-ID: 87psxvh8kt.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Benjamin Smith <lists(at)benjamindsmith(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2005-03-19 17:25:32 Re: pseudo-serial values in dual primary key?
Previous Message Tom Lane 2005-03-19 16:21:49 Re: pg_dump fails with socket_not_open