From: Volkan YAZICI <yazicivo@ttmail.com>
To: "John Reeve" <jreeve@pelagodesign.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Sequential non unique IDs
References: 
 <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com>
Date: Wed, 02 Apr 2008 08:48:03 +0300
In-Reply-To: 
 <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com>
	(John Reeve's message of "Tue, 1 Apr 2008 15:49:46 -0700")
Message-ID: <87k5jgyfho.fsf@alamut.mobiliz.com.tr>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.0.60 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

On Tue, 1 Apr 2008, "John Reeve" <jreeve@pelagodesign.com> writes:

> I have the following scenario:
>  
> A 'task' table that has the fields:
>     id => primary key, updated on each insert using a sequence
>     customerid => integer
>     localid => integer
>  
> I need the localid to be sequential and unique per unique customerid. The data needs to look like this:
>     1, 92, 1
>     2, 92, 2
>     3, 93, 1
>     4, 93, 2
>     5, 93, 3
>     6, 92, 3
>     and so on
>  
> I am presently doing this on the INSERT using an INNER SELECT, like this:
>  
> INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'),
> 92, (SELECT MAX(localid) + 1 FROM task WHERE customerid = 92));

Why not creating a separate serial for localid field? It won't
contradict with your making localid to be sequential and unique per
unique customerid restriction.

CREATE TABLE task (
    id            serial    PRIMARY KEY,
    customerid    integer,
    localid       serial
);

CREATE UNIQUE INDEX task_customerid_localid_idx
    ON task (customerid, localid);

INSERT INTO task (customerid) VALUES (92);

If I didn't get you wrong, this should solve your problem.


Regards.