I have a table which contains 69000 records. Now, how to add a new column(say index) which should contain the value 1 for 1st row, 2 for 2nd row ....etc.
I want the following result
id name index
--------------------------
9796896 sandy 1
796869 raj 2
Add the column and update it with something like that :
with cte as
(
select *
, rank() over (order by id desc) as ranker
from test
)
update test
set index = ranker
from cte
where cte.id = test.id;
you can use identity column of the oracle:
alter table your_Table add index_ number GENERATED by default as IDENTITY;
also, you can add the column and then assign value to it:
alter table your_Table add index_ number;
update your_Table tt
set tt.index_ = (select rn from (
select row_number() over (order by id desc) rn
from your_Table t
) t where t.id = tt.id)
Cheers!!
This worked nicely for me running postgres 9.6:
ALTER TABLE test ADD COLUMN index BIGSERIAL ;
Short one liner and it keeps on auto-incrementing in future inserts.
Although I think the count started at zero. I'll update answer after I checked.
