I have a table has more than 1000000 rows which was imported from CSV file. The table did not have a primary key, I want to add one with incremented bigint to the table. This is my table DDL:
CREATE TABLE public.ecdict (
word varchar(256) NULL,
phonetic varchar(512) NULL,
definition varchar(80000) NULL,
"translation" varchar(80000) NULL,
pos varchar(100) NULL,
collins int4 NULL,
oxford int4 NULL,
tag varchar(128) NULL,
bnc int4 NULL,
frq int4 NULL,
exchange varchar(128) NULL,
detail varchar(2000) NULL,
audio varchar(8) NULL,
id int8 NULL
);
The id
column is new add and all data be NULL, I tried to use this command:
update ecdict c
set id = c2.seqnum
from (
select c2.*, row_number() over () as seqnum
from ecdict c2
) c2
where c2.pkid = c.pkid;
But I could not found an unique column for the table for join, what should I do to add the id
column with incremented bigint
values?