Greenplum add a column to table and insert value to it in incremental order

All we need is an easy explanation of the problem, so here it is.

I have a table st.student like following, which has one column.

STUDENT_ID
100001
100002
100003
100004
100005
100006

I need to insert a column to this and values in it should be incremental one. Like the following. Note that NEW_STUDENT_ID can start from any value. Once it starts, it’s continuous. What’s the query for this ?

STUDENT_ID NEW_STUDENT_ID
100001 349009
100002 349010
100003 349011
100004 349012
100005 349013
100006 349014

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

One way is altering the table using:

alter table student add column NEW_STUDENT_ID   serial;

Result:

student_id  new_student_id
100001          1
100002          2
100003          3
100004          4
100005          5
100006          6

Demo

Another way, create a sequence with custom start and alter the table with using the created sequence:

CREATE SEQUENCE new_student_id START 349009;

alter table  student add column   new_student_id int DEFAULT  nextval('new_student_id') NOT NULL;

Demo

Reference

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply