How to set auto increment primary key in PostgreSQL?

Try this command:

ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

Try it with the same DB-user as the one you have created the table.

Auto incrementing primary key in postgresql:

Step 1, create your table:

CREATE TABLE epictable(    mytable_key    serial primary key,    moobars        VARCHAR(40) not null,    foobars        DATE);

Step 2, insert values into your table like this, notice that mytable_key is not specified in the first parameter list, this causes the default sequence to autoincrement.

insert into epictable(moobars,foobars) values('delicious moobars','2012-05-01')insert into epictable(moobars,foobars) values('worldwide interblag','2012-05-02')

Step 3, select * from your table:

el@voyager$ psql -U pgadmin -d kurz_prod -c "select * from epictable"

Step 4, interpret the output:

mytable_key  |        moobars        |  foobars   -------------+-----------------------+------------           1 | delicious moobars     | 2012-05-01           2 | world wide interblags | 2012-05-02(2 rows)

Observe that mytable_key column has been auto incremented.

ProTip:

You should always be using a primary key on your table because postgresql internally uses hash table structures to increase the speed of inserts, deletes, updates and selects. If a primary key column (which is forced unique and non-null) is available, it can be depended on to provide a unique seed for the hash function. If no primary key column is available, the hash function becomes inefficient as it selects some other set of columns as a key.

Create an auto incrementing primary key in postgresql, using a custom sequence:

Step 1, create your sequence:

create sequence splog_adfarm_seq    start 1    increment 1    NO MAXVALUE    CACHE 1;ALTER TABLE fact_stock_data_detail_seqOWNER TO pgadmin;

Step 2, create your table

CREATE TABLE splog_adfarm(    splog_key    INT unique not null,    splog_value  VARCHAR(100) not null);

Step 3, insert into your table

insert into splog_adfarm values (    nextval('splog_adfarm_seq'),     'Is your family tree a directed acyclic graph?');insert into splog_adfarm values (    nextval('splog_adfarm_seq'),     'Will the smart cookies catch the crumb?  Find out now!');

Step 4, observe the rows

el@defiant ~ $ psql -U pgadmin -d kurz_prod -c "select * from splog_adfarm"splog_key |                            splog_value                             ----------+--------------------------------------------------------------------        1 | Is your family tree a directed acyclic graph?        2 | Will the smart cookies catch the crumb?  Find out now!(3 rows)

The two rows have keys that start at 1 and are incremented by 1, as defined by the sequence.

Bonus Elite ProTip:

Programmers hate typing, and typing out the nextval('splog_adfarm_seq') is annoying. You can type DEFAULT for that parameter instead, like this:

insert into splog_adfarm values (    DEFAULT,     'Sufficient intelligence to outwit a thimble.');

For the above to work, you have to define a default value for that key column on splog_adfarm table. Which is prettier.