how to update id as row number in PostgreSQL 13

Posted on

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 :

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;

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

SET = r.newId
FROM _article_favorites a
INNER JOIN _article_favorites_rowids r
    ON a.uniqueField = r.uniqueField

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.

Leave a Reply

Your email address will not be published. Required fields are marked *