Обсуждение: generating part of composite key

I have a table with a composite PK like

CREATE TABLE t (
    grp INT NOT NULL,
    itm SMALLINT NOT NULL,
    ...,
    PRIMARY KEY (grp,itm));

Normally the app takes care of providing the correct
grp,itm values when inserting records.  However
(during a long period of development), I need to
repeatedly reload data into the table from a data
source (a select statement) that has grp values but
no itm values.  These itm values need to be small
numbers (1 to COUNT(itm) for each grp value) and
capture the order in which the data was generated
by the select.

MySql seems to have an auto_number function(?) that
takes an optional argument which would be grp in this
case, that (judging from the manual, I don't actually use
MySql) gives the behavior I want (restarts numbering
from 1 when grp value changes).

I am wondering if there is a way of doing it in Postgresql
that is better than the ways I've thought of so far (all are
fairly unpleasant compared to the MySql solution):
- Load into a temp table with itm column defined as INT
  and a default sequence.  Renumber after the load, then
  copy into the real table.
- Assign the itm values with a trigger added before the
  load proccess and removed after.
- Wrap the select that generates the load data in another
  select that will create the itm values (currently the grp's
  contain at most a half-dozen itm's but that could change
  to a sew grops with the several tens of thousands itm's
  in a few grp's in the near future).  There are potentialy
  100000's of grp values.  Not sure what this select would
  look like though.

I thought that generating this kind of composite key might
be a common problem.  Any suggestions?  TIA...

On Jul 26, 2007, at 5:57 PM, Stuart wrote:
> I have a table with a composite PK like
>
> CREATE TABLE t (
>     grp INT NOT NULL,
>     itm SMALLINT NOT NULL,
>     ...,
>     PRIMARY KEY (grp,itm));
>
> Normally the app takes care of providing the correct
> grp,itm values when inserting records.  However
> (during a long period of development), I need to
> repeatedly reload data into the table from a data
> source (a select statement) that has grp values but
> no itm values.  These itm values need to be small
> numbers (1 to COUNT(itm) for each grp value) and
> capture the order in which the data was generated
> by the select.
>
> MySql seems to have an auto_number function(?) that
> takes an optional argument which would be grp in this
> case, that (judging from the manual, I don't actually use
> MySql) gives the behavior I want (restarts numbering
> from 1 when grp value changes).

I'd recommend writing a function in a language that allows you to
store state information between calls, such as plperl and have it
handle the counting, reseting the count every time grp changes. Of
course that means you need to order by grp in your select (and grp
has to be the first sort key). If you can't do that, your next best
bet is to populate itm with a sequence (not resetting) and then
adjust itm after the fact by selecting min(itm) ... group by grp.
Might want to do that in a temp table to avoid bloating the main table.

Note that anything that involves resetting a sequence or anything
like that is going to be a big race condition if you have multiple
inserting processes.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)