Filling in Holes in Numeric Ordering in PostgreSQL

DISCLAIMER: This can be used to fill in holes in columns generated using Postgres-managed sequences (e.g. columns with DEFAULT nextval(‘my_sequence’), as long as you also reset the value of the sequence after using:

SELECT SETVAL('my_sequence', (
   SELECT MAX(id)
   FROM my_sequenced_table)
);

so that the next time you insert to the table, the sequence is on the desired value (the next one after your current highest value). Rule of thumb: tread carefully anytime you’re resetting values on a sequence. The scenario that generated this solution was an ordering in a column that was manually managed and not serving as a key or identifier.

One of our applications stores the display order of visual elements on a given webpage in a table (because this is customizable by client). Here is a dummy-version of the table:

I had to remove a few elements from a particular page (page_id 1 in this example), leaving a spotty display order: [0,2,3,5] instead of [0,1,2,3,4,5].

An ugly and somewhat misleading display_order column

How can I fix the holes in the display_order for that particular page? Enter Postgres’s ROW_NUMBER/OVER function:

UPDATE my_table
   SET display_order = tt.row_number
   FROM (
      SELECT (ROW_NUMBER() OVER(ORDER BY display_order)-1) AS row_number,
      element_id AS element_id
      FROM my_table
      WHERE page_id = 1) AS tt
   WHERE my_table.element_id = tt.element_id
   AND page_id = 1;

ROW_NUMBER() OVER() can be used as part of a SELECT statement to generate a pseudocolumn (meaning that it just appears for your output, and is not stored in the table itself) of row numbers. As an example, running the internal SELECT statement alone returns data that looks like the table below. Please note that I subtract 1 from the row number so that my row numbers starts with 0 — normally, ROW_NUMBER() starts with 1.

For my solution, I use the ROW_NUMBER function to generate that pseudocolumn as part of a temporary table. That temporary table also includes element_id, which is the key I use between the temporary table and the actual table to update the correct rows. (I also limited the scope of the changes to just the page_id I wanted to update in this example.)

Unsurprisingly, ORDER IS EXTREMELY IMPORTANT IN SETTING THIS UP. The OVER() function determines the ordering of the generated row numbers. In this case, I ordered by display_order so that the overall order remained, but the numeric holes would be filled in.

My resulting table looks like this:

A nice clean display order column