> Ideally, I'd like to figure out a single SQL query that can be run
> afterwards to clean up the dsply_order to make sure that each number occurs
> only one time and that there are no gaps.
Well... by far the easiest way to approach this is not to clean up the
gaps. Removing gaps will only make things pretty, not easier or faster.
This is one of the many times it is best to differentiate between what
is displayed and what is used for functional purposes.
CREATE TEMPORARY SEQUENCE images_display_count; SELECT nextval('images_display_count') AS display_order
, * FROM images WHERE galleryid = 1 ORDER BY real_order; DROP SEQUENCE
images_display_count;
There are ways of replacing the sequence that may be faster, but this
will address your concern. Do your updates, etc. via real_order and show
the user display_order.