Re: adding data to tables with sequences

"Jennifer Lee" <jlee(at)scri(dot)sari(dot)ac(dot)uk> writes:
> what I've tried is
> INSERT INTO data_value (data_id, element_number, value)
> SELECT d.data_id, nextval('element_seq'), t.value
> FROM data d, temp_data t, data_type dt
> WHERE d.type_id = dt.type_id
> AND t.marker = dt.description
> ORDER BY t.temp_id;

> This works only the element_numbers using the sequences are in a
> random order. The values seem to be inserted in the order of the
> temp_id so if I do a select * from the table the are listed in the order I'd
> like them in.

Well, yeah: you asked to ORDER BY temp_id, and that happens after the
SELECT-list expressions are computed. You could probably get the
behavior you want with a two-level SELECT:

INSERT INTO data_value (data_id, element_number, value)
SELECT data_id, nextval('element_seq'), value
FROM
(SELECT d.data_id, t.value
FROM data d, temp_data t, data_type dt
WHERE d.type_id = dt.type_id
AND t.marker = dt.description
ORDER BY t.temp_id) ss;

regards, tom lane