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



  • 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


  • 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
    

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=a5ac169d1f0304be1829d4b0e7d49873

    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;

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=c3106f36917c293005b5019da9bbb3a3

    https://www.postgresql.org/docs/current/sql-createsequence.html




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2