Home > PostgreSQL > Add auto increment column in PostgreSQL

Add auto increment column in PostgreSQL

In PostgreSQL, we cannot just add an column and mark it as auto increment like in MySQL or SQL Server. Instead, we have to create an sequence and link it to the specified column.

1. Assume that we have a table called [testtbl] with an unique column called [id]

2. Generate sequence

CREATE SEQUENCE <Sequence name>

=>

CREATE SEQUENCE <testtbl_id_seq>

※After the sequence’s already created, we can call NEXTVAL(‘<Sequence name>’) to generate a new value automatically.


3. Link the sequence to the unique column

ALTER TABLE <Table name>
ALTER COLUMN <Column name>
SET DEFAULT NEXTVAL(<Created sequence name>);

=>

ALTER TABLE testtbl
ALTER COLUMN id
SET DEFAULT NEXTVAL('testtbl_id_seq');

That’s all what we have to do.

Advertisements
Categories: PostgreSQL Tags:
  1. Joachim
    September 18, 2009 at 9:30 PM

    How about this:
    ALTER TABLE ADD COLUMN id SERIAL

    • June 22, 2011 at 9:01 PM

      Only one minus serial type range is 1 to 2147483647 😦

      • Canisaur
        December 21, 2011 at 10:57 PM

        If you need a larger range use BIGSERIAL instead, then your range is 1 to 9223372036854775807 (but you use twice as much memory per entry)

  2. Phu
    September 21, 2009 at 8:34 AM

    Hi Joachim, your way of setting is also ok. This is the other way to add auto increment with postgresql. Thanks for your supplementary.

  3. February 15, 2010 at 3:42 PM

    there is serial type ? are you ok

  4. sidik
    May 10, 2010 at 5:09 PM

    good job

  5. Xhec
    May 10, 2010 at 9:42 PM

    so simple, this is how examples should be done thanks 😀

  6. September 6, 2010 at 9:57 AM

    hi, thanks for nice post !

  7. Rain
    November 4, 2011 at 4:40 PM

    Great example.
    Simply understandable, concise, clean.

    No drama before get to the point like other blogs.

  8. December 25, 2011 at 12:00 AM

    Helpful info. Fortunate me I found your website by chance, and I am shocked why this coincidence didn’t came about earlier! I bookmarked it.

  9. May 30, 2012 at 8:49 AM

    Wanted to drop a remark and let you know your Feed isnt functioning today. I tried including it to my Yahoo reader account and got nothing.

  10. May 21, 2013 at 8:12 AM

    My partner and I stumbled over here from a different website and
    thought I may as well check things out. I like what I see so
    now i’m following you. Look forward to exploring your web page repeatedly.

  1. August 13, 2009 at 3:46 AM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: