David Ghedini

Linux, Java, Oracle, and PostgreSQL


David Ghedini

Sunday Feb 12, 2012

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.

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

Bookmark and Share



Comments:

nice postnice

Posted by 223.239.203.48 on October 11, 2012 at 12:47 AM CDT #

Excellent

Posted by Pedro Hernández on June 05, 2013 at 02:43 PM CDT #

Post a Comment:
  • HTML Syntax: Allowed

Main Menu

Built With

Search

Pages

LinkedIn

Technorati Profile

Add Technorati Favorite

Tag Cloud

Enciva Solutions

Navigation

Visitors

Sponsors

Feeds

Tag Cloud

VPS Hosting: 1Gbps Network