data dependent sequences?

From: "Stuart McGraw" <smcg2297(at)frii(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: data dependent sequences?
Date: 2007-07-15 15:28:26
Message-ID: AKEFJAHAPDBEDKIICNJKMEJPCDAA.smcg2297@frii.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Advice requested :-) I have a table like:

CREATE TABLE items (
id INT,
typ INT ...
PRIMAY KEY (seq,typ));

I would like 'id' to be like a SERIAL except that I
want independent sequences for each value of 'typ'.

So if 'items' is:

id typ
----+-----
1 'a'
2 'a'
3 'a'
1 'b'

then doing:

INSERT items(typ) VALUES('a');
INSERT items(typ) VALUES('b');

will result in:

id typ
----+-----
1 'a'
2 'a'
3 'a'
1 'b'
4 'a'
2 'b'

Because there can be dozens of values of 'typ'
and new ones added not infrequently, creating
a postgresql sequence for each seems awkward.

Are there other ways I could accomplish this?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ragnar 2007-07-15 16:04:56 Re: data dependent sequences?
Previous Message Andrej Ricnik-Bay 2007-07-14 01:42:26 Re: Converting from MS Access field aliases