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