From: Volkan YAZICI <yazicivo@ttmail.com>
To: John Reeve <jreeve@pelagodesign.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sequential non unique IDs
Date: Wed, 02 Apr 2008 08:48:03 +0300
Message-ID: <87k5jgyfho.fsf@alamut.mobiliz.com.tr> (raw)
In-Reply-To: <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com>
References: <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: yazicivo@ttmail.com, jreeve@pelagodesign.com
Subject: Re: Sequential non unique IDs
In-Reply-To: <87k5jgyfho.fsf@alamut.mobiliz.com.tr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox