Re: adding data to tables with sequences

"Jennifer Lee" <jlee@xxxxxxxxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)