How to increment ID for each INSERT?

Question

In PostgreSQL I have table comments with primary key comment_id (VARCHAR of length 4). I have a form to insert a comment. How to get my Java Servlet to increment comment_id for each INSERT (0001 to 0002)?

Answer 1

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');

Answer 2

Update: in Postgres 10 or later, consider an IDENTITY column instead. See:

• Auto increment table column

---

Use the serial pseudo data type to begin with. It creates and attaches the sequence object automatically and sets the DEFAULT to nextval() from the sequence. It does all you need. Effective type for the column is integer. There is also bigserial and smallserial (in Postgres 9.2+). Just follow the link to the manual.

CREATE TABLE comments (
  comment_id serial PRIMARY KEY
, comment text NOT NULL
);

You can ignore the column for INSERT commands:

INSERT INTO my_comment (comment)
VALUES ('My comment here');

comment_id is filled in automatically.

But you should always provide a target column list for an INSERT command. If you later change table layout, your query may break in hurtful ways. It may be ok to skip the column list for ad-hoc commands or when the table structure is guaranteed (like when you created the table in the same line of code). Other than that, provide a column list!

If you want the resulting comment_id back, without another round trip to the server:

INSERT INTO my_comment (comment)
VALUES ('My comment here');
RETURNING comment_id;

Details in the manual here.