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

137,073
23
153
219

answered Sep 06 '19 at 09:51

Jaisus

1,019
5
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

640
4
16

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

score 0 · Answer 4 · answered Jan 16 '23 at 18:57

0

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.

answered Jan 16 '23 at 18:57

Julien Lamarche

931
1
12
29

Add incrementing value to the column in postgresql

4 Answers4