postgresql alter table add column serial with sort values on created_at

Question

I have user tables and now I want to add one serial column cid:

user:

 id    | username | email           | createdAt
-------------------------------------------------
"uuid" | "abc"    | "[email protected]" | '2017-01-01'
"uuid" | "abc"    | "[email protected]" | '2017-02-01'
"uuid" | "abc"    | "[email protected]" | '2017-03-01'

I want to add a column cid with values like serial but not primary key

  id   |username|     email       | createdAt    | cid
-------------------------------------------------------
"uuid" | "abc"  | "[email protected]" | '2017-01-01' |  1
"uuid" | "abc"  | "[email protected]" | '2017-02-01' |  2
"uuid" | "abc"  | "[email protected]" | '2017-03-01' |  3
...

What I tried:

alter table user add column cid serial not null;

but it generates:

  id   |username|     email       | createdAt    | cid
-------------------------------------------------------
"uuid" | "abc"  | "[email protected]" | '2017-01-01' | 4
"uuid" | "abc"  | "[email protected]" | '2017-02-01' | 7
"uuid" | "abc"  | "[email protected]" | '2017-03-01' | 3
....

can I do something like:

alter table user add column cid serial not null order by createdAt

So that it can generate the expected result which is cid sequential values with order by createdAt?

Gordon Linoff · Accepted Answer · 2017-07-21 02:46:47Z

1

Why add it to the table? You can just do:

select u.*, row_number() over (order by u.createdAt) as seqnum
from user u;

If you have an index on user(createdAt), then this should take advantage of the index.

If you have a unique column on the table, you can do an update:

update user u set cid = uu.seqnum from (select u.*, row_number() over (order by u.createdAt) as seqnum from user u ) uu on u.uuid = uu.uuid

edited Jul 21, 2017 at 2:46

answered Jul 20, 2017 at 19:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rajarshi Das Over a year ago

Thank you for giving a quick answer.....yes I have to add the column as my id column is an uuid so need a seperate column which has a sequential number

Rajarshi Das Over a year ago

I also tried update user set cid = row_number() over (order by user.createdAt) but throwing error

Collectives™ on Stack Overflow

postgresql alter table add column serial with sort values on created_at

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related