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?
Using a sequence might be faster than using
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 ) UPDATE a SET a.id = 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
serial (represent integer data type) instead of
bigserial (bigint data type). These types are not true types. here is document link related.