David Ghedini
Sunday Feb 12, 2012
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.
Step 1: Create a Sequence
testdb=> CREATE SEQUENCE mgr_id_seq; CREATE SEQUENCE
Step 2. Create Table and Set the Column Default
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=>
Step 3. Insert Data.
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
Step 4. Select on Table to View Sequence.
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.
Note: Auto Increment in PhpPgAdmin
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[3] | Tags: | Export to: |

Posted by 223.239.203.48 on October 11, 2012 at 12:47 AM CDT #
Posted by Pedro Hernández on June 05, 2013 at 02:43 PM CDT #
Posted by Anvesh on August 03, 2016 at 02:47 PM CDT #