How to update with incrementing value

Question

I have a table in PostgreSQL that has an ID column that is supposed to be unique. However, a large number of the rows (around 3 million) currently have an ID of "1".

What I know:

• The total number of rows
• The current maximum value for the ID column
• The number of rows with an (incorrect) ID of "1"

What I need is a query that will pull all the rows with an ID of "1" and assign them a new ID that increments automatically so that every row in the table will have a unique ID. I'd like it to start at the currentMaxId + 1 and assign each row the subsequent ID.

This is the closest I've gotten with a query:

UPDATE table_name
SET id = (
    SELECT max(id) FROM table_name
) + 1
WHERE id = '1'

The problem with this is that the inner SELECT only runs the first time, thus setting the ID of the rows in question to the original max(id) + 1, not the new max(id) + 1 every time, giving me the same problem I'm trying to solve.
Any suggestions on how to tweak this query to achieve my desired result or an alternative method would be greatly appreciated!

Answer 1

You may do it step by step with a temporary sequence.

1) creation

create temporary sequence seq_upd;

2) set it to the proper initial value

select setval('seq_upd', (select max(id) from table_name));

3) update

update table_name set id=nextval('seq_upd') where id=1;

Answer 2

If you are going to work with a SEQUENCE, consider the serial pseudo data type for you id. Then you can just draw nextval() from the "owned" (not temporary) sequence, which will then be up to date automatically.

If you don't want that, you can fall back to using the ctid and row_number() for a one-time numbering:

UPDATE tbl t
SET    id = x.max_id + u.rn
FROM  (SELECT max(id) AS max_id FROM tbl) x
    , (SELECT ctid, row_number() OVER (ORDER BY ctid) AS rn
       FROM   tbl WHERE id = 1) u
WHERE  t.ctid = u.ctid;

Related answer on dba.SE:
numbering rows consecutively for a number of tables