CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
rajashekar
rajashekar

Reputation: 689

Add incrementing value to the column in postgresql

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

Upvotes: 1

Views: 1870

Answers (4)

Julien Lamarche
Julien Lamarche

Reputation: 1050

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.

Upvotes: 0

Jaisus
Jaisus

Reputation: 1109

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;

Upvotes: 3

Popeye
Popeye

Reputation: 35930

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!!

Upvotes: 0

Ankit Das
Ankit Das

Reputation: 650

Try this: 

ALTER TABLE Tablename
    ADD COLUMN index int               
        GENERATED BY DEFAULT AS IDENTITY;

Upvotes: 0

Related Questions