Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 6 down vote accepted

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

CREATE SEQUENCE your_seq;
CREATE TABLE foo(
  id int default nextval('your_seq'::regclass),
  other_column TEXT
);
INSERT INTO foo(other_column) VALUES ('bar') RETURNING *;
share|improve this answer

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.

share|improve this answer
1  
I disagree about the "easier in PgAdmin". You think going through clicks is easier than simply making a column SERIAL? – ypercubeᵀᴹ Oct 30 '15 at 8:27
    
Shall I say easier for those who are used to GUIs and If youre not familiar with command lines. :) – toing_toing Oct 30 '15 at 8:41
    
@NisalUpendra the problem is, if you don't know what is happening in the background when clicking around in a GUI, you might get into trouble. – dezso Oct 30 '15 at 9:11
    
Completely agree. I have been stuck like that. – toing_toing Oct 30 '15 at 9:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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