PostgreSQL Auto Increment

This post will demonstrate how to auto increment on a column in PostgreSQL.

In our example we will create a table, Managers.

Our table will have three columns: mgr_id, mgr_name, and mgr_email.

For each insertion of a new Manager entry, we want to auto increment our primary key, mgr_id, by 1.

testdb=> CREATE SEQUENCE mgr_id_seq;
CREATE SEQUENCE

We now create the Manager table.

For mgr_id, set as PRIMARY KEY and set the DEFAULT to NEXTVAL('mgr_id_seq') as shown below.

This will increment our sequence by 1 each time a new Manager entry is inserted.

testdb=> CREATE TABLE managers(
testdb(> mgr_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('mgr_id_seq'),
testdb(> mgr_name VARCHAR(50),
testdb(> mgr_email VARCHAR(50)
testdb(> );

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "managers_pkey" for table "managers"
CREATE TABLE
testdb=>

testdb=> INSERT INTO managers (mgr_name, mgr_email) VALUES('bob smith', 'bob@smith.com');
INSERT 0 1
testdb=> INSERT INTO managers (mgr_name, mgr_email) VALUES('tom jones', 'tom@jones.com');
INSERT 0 1

testdb=> select * from managers;
 mgr_id | mgr_name  |   mgr_email
--------+-----------+---------------
      1 | bob smith | bob@smith.com
      2 | tom jones | tom@jones.com
(2 rows)

testdb=>

As we an see from above, the increment begins at 1 and increments by 1 by default.







Unlike PhpMyAdmin, PhpPgAdmin does not have a handy drop-down to auto increment a column when you create a table.

You can simply use the PhpPgAdmin SQL Editor to create the sequence and table as shown in steps 1 to 3 above.

Alternatively, you can create your sequence using the Sequence node in PhpPgAdmin as shown below:



With your sequence created, you can then set the DEFAULT value for the mgr_id column to nextval('mgr_id_seq') in the table creation GUI as shown below:




More on Sequences in PostgreSQL:

http://www.postgresql.org/docs/9.0/static/sql-createsequence.html

http://www.postgresql.org/docs/9.1/static/functions-sequence.html

Posted at 06:44AM Feb 12, 2012 by David in PostgreSQL  |  Comments[1] | Tags: | Export to: |