how to update id as row number in PostgreSQL 13

Question

My talbe article_favorites did not have row number, right now it contains 100000 record. I want to add a bigint id column in the talbe and set the id as row number by default. I tried this sql:

update article_favorites set id = row_number() OVER ()

but it tell me the window functions are not allowed in UPDATE, what should I do to update the id as row number?

Answer 1

Using a sequence might be faster than using row_number()

create sequence id_seq;
update article_favorites 
 set id = nextval('id_seq');
drop sequence id_seq;

• Rafs: I guess we should use a temp sequence here: CREATE TEMP SEQUENCE .. which will be dropped automatically, just in case.

Answer 2

As the error you're encountering mentions, window functions can't be used in an UPDATE statement BUT you can define them ahead of time in a CTE or subquery, and use their evaluated results in the UPDATE statement. IF you have a unique way to identify the row already, then you can accomplish your goal like so:

WITH _article_favorites_rowids AS
(
 SELECT uniqueField, ROW_NUMBER() OVER () AS newId
 FROM _article_favorites
)
UPDATE a
SET a.id = r.newId
FROM _article_favorites a
INNER JOIN _article_favorites_rowids r
 ON a.uniqueField = r.uniqueField

• Rafs: uniqueField is usually the id, so it is hard to use another uniqueField, and I don't think we can use window functions in where clauses or join conditions. Any ideas if we don't have a usable uniqueField to link the data for the update?
• J.D.: @Rafs I don't follow. Does your data have a uniqueField?
• Rafs: No, no other unique fields apart from id

Answer 3

You can add generated values when you create id column. The statement below creates a auto generated column and fills if for existing records.

ALTER TABLE article_favorites ADD id bigserial;

You can create primary key if your table does not have one or you can just add unique key to that field.

ALTER TABLE article_favorites ADD id bigserial PRIMARY KEY; -- or UNIQUE 

You can use smallserial or serial (represent integer data type) instead of bigserial (bigint data type). These types are not true types. here is document link related.