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