[FIXED] Increment value of a table ID for each INSERT

Issue

I'm using PostgreSQL with all my tables setup. I currently have a table called comments with a primary key called comment_id which is a VARCHAR of length 4.

I have a form setup to insert a new comment into the database but I'm confused as to how I will get my Java servlet to ++ the comment_id from it's previous value. E.g. 0001 to 0002.

Solution

You don't want to use a VARCHAR for your id column. In postgres you can create a sequence and then get the next value of that sequence for each insert.

here are the docs

Basically, you do something like

CREATE SEQUENCE mysequence START 101

Then, when you insert you do something like

INSERT INTO my_comment values (nextval('mysequence'), 'this is my comment');

Answered By - digitaljoel
Answer Checked By - Senaida (JavaFixing Volunteer)