I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application.

If I create or have created a table with pgAdmin III, how can I add "auto-increment"-functionality on a column id that has type integer?

3 Answers 3


two options: Use the "datatype" SERIAL or create a sequence and use this sequence as a default value for your integer:

  id int default nextval('your_seq'::regclass),
  other_column TEXT
INSERT INTO foo(other_column) VALUES ('bar') RETURNING *;
  • 3
    This answer could be updated to include GENERATED BY DEFAULT AS IDENTITY introduced with Postgres >= 10.
    – Madbreaks
    Commented Jun 11, 2019 at 16:38
  • @Madbreaks Ain't nobody got time for that!
    – NoName
    Commented Jan 25, 2022 at 21:23

This is pretty easy in pgAdmin 4.

First, add a column to your table, then click the little edit icon:

enter image description here

Then go to Constraints and select the Identity type:

enter image description here

Save your table and the column will auto-increment.

  • 1
    What is point of cache here? Commented Aug 8, 2021 at 16:16
  • I could be wrong. but if it's like Oracle, it stores a certain number (to be defined in the cache field) of values in memory for quicker assignment. Rather than having to re-calculate the value each time a row is added. The values could be lost (resulting in gaps of the identity column) if the DB crashes etc. Commented Dec 7, 2021 at 2:07

If you want to do this in PGAdmin, it is much easier than using the command line. It seems in PostgreSQL, to add a auto increment to a column, we first need to create a auto increment sequence and add it to the required column. I did like this.

1) Firstly you need to make sure there is a primary key for your table. Also keep the data type of the primary key in bigint or smallint. (I used bigint, could not find a datatype called serial as mentioned in other answers elsewhere)

2)Then add a sequence by right clicking on sequence-> add new sequence. If there is no data in the table, leave the sequence as it is, don't make any changes. Just save it. If there is existing data, add the last or highest value in the primary key column to the Current value in Definitions tab as shown below. enter image description here

3)Finally, add the line nextval('your_sequence_name'::regclass) to the Default value in your primary key as shown below.

enter image description here Make sure the sequence name is correct here. This is all and auto increment should work.

  • 4
    I disagree about the "easier in PgAdmin". You think going through clicks is easier than simply making a column SERIAL? Commented Oct 30, 2015 at 8:27
  • 1
    Shall I say easier for those who are used to GUIs and If youre not familiar with command lines. :) Commented Oct 30, 2015 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.