Re: adding data to tables with sequences

"Jennifer Lee" <jlee@scri.sari.ac.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