Add incrementing value to the column in postgresql

Question

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

Why have you tagged this with Oracle if you are using PostgreSQL? (Also 'index' seems like a poor choice name for a column... it wouldn't be legal in Oracle, unless quoted.) — Alex Poole, Sep 06 '19 at 09:58
Possible duplicate of [PostgreSQL Autoincrement](https://stackoverflow.com/questions/787722/postgresql-autoincrement) — Ankit Das, Sep 06 '19 at 10:19
Check this out https://www.tutorialspoint.com/postgresql/postgresql_using_autoincrement.htm# — Chathuranga Ranasinghe, Sep 06 '19 at 10:33

score 2 · Accepted Answer · edited Sep 06 '19 at 12:03

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;

edited Sep 06 '19 at 12:03

Jean-François Fabre

131,796
23
122
195

answered Sep 06 '19 at 09:51

Jaisus

919
3
14

score 0 · Answer 2 · answered Sep 06 '19 at 09:49

0

Try this:

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

answered Sep 06 '19 at 09:49

Ankit Das

587
3
14

generated columns ? Are they not available only for version 12 ? – Jaisus Sep 06 '19 at 09:55

score 0 · Answer 3 · answered Sep 06 '19 at 09:50

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

Add incrementing value to the column in postgresql

3 Answers3