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...
