A few years ago I asked about creating a single UPDATE statement to assign id's from a sequence, with the sequences applied in a particular order. In other words, order the table, then apply nextval-generated id's to the id field in question.
Here is the original post: http://archives.postgresql.org/pgsql-general/2009-04/msg01123.php The explanation and solution provided made total sense. To my surprise, every now and then users still report sequences being applied out of order. Below is the code in question. I admit that I am completely baffled. Does anyone have any clues? UPDATE my_schema.my_table SET id = the_next.id FROM ( SELECT order_by_value, nextval('my_schema.id_seq') AS id FROM ( SELECT order_by_value FROM my_schema.my_table WHERE id IS NULL ORDER BY order_by_value ) AS ordered ) AS the_next WHERE my_table.order_by_value = the_next.order_by_value AND my_table.id IS NULL; I should mention that this is an example only - the actual code is dynamic SQL within a PL/PGSQL stored proc. The articles my_schema, my_table and order_by_value will be replaced by variables and the code above would be a string that is then fed to an EXECUTE statement. Would that make ANY sort of a difference? Thanks! Carlo
