generating part of composite key

Поиск
Список
Период
Сортировка
От Stuart
Тема generating part of composite key
Дата
Msg-id f8bftp$8he$1@sea.gmane.org
обсуждение исходный текст
Ответы Re: generating part of composite key  (Jim Nasby <decibel@decibel.org>)
Список pgsql-general
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...


В списке pgsql-general по дате отправления:

Предыдущее
От: John Koller
Дата:
Сообщение: Re: Question about Postgres
Следующее
От: Luca Ferrari
Дата:
Сообщение: Re: a few questions (and doubts) about xid