Skip site navigation (1) Skip section navigation (2)

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

From: Shachar Shemesh <psql(at)shemesh(dot)biz>
To: Paulovič Michal <michal(at)paulovic(dot)sk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: SERIAL type - auto-increment grouped by other field
Date: 2004-02-29 06:47:42
Message-ID: 40418B0E.3000805@shemesh.biz (view raw or flat)
Thread:
Lists: pgsql-general
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/


In response to

pgsql-general by date

Next:From: Gregory WoodDate: 2004-02-29 14:11:20
Subject: Re: Triggers per transaction, workaround? prospects?
Previous:From: Tom LaneDate: 2004-02-29 04:14:28
Subject: Re: Korean characters in a SQL_ASCII database?!?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group