Re: SERIAL type - auto-increment grouped by other field

Paulovič Michal wrote:

>hi all,
>
>I have problem with SERIAL field type (or sequence functionality).
>I have table with three columns - ID, IDS, NAME.
>I want auto-increment IDS grouped by ID.
>Example:
>1, 1, Ferdo
>1, 2, John
>2, 1, Martin
>1, 3, Elvira
>2, 2, Georgia
>
>but...
>when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is:
>1, 1, Ferdo
>1, 2, John
>2, 3, Martin
>1, 4, Elvira
>2, 5, Georgia
>
>where i make misstake??? how can i do it??? in documentation there is
>description only for one auto-increment column. I didn't find auto increment as
>I described upper. Do you have any idea how can I do it???
>
>tnx a lot
>
>
>
A sequence (which is what a serial is) does not promise you consecutive
numbers. For example - try out the following:
begin a transaction
insert a new row to your table
rollback the transaction

Now add a new row. You will see that the new row did not get the number
that was assigned inside the transaction. All a sequence promises you is
uniqueness (within the 2^32 limit), and that promise is kept for your
example as well.

I can think of something that may help you out here, but you will have
to tell in advance how many groups you will need (how many IDs). Just
create that many sequences and put the id into the sequence name. Then
put in a default value based on that.

--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/