|
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!
|
