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=&gt; CREATE SEQUENCE mgr_id_seq;
CREATE SEQUENCE

1 2	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=&gt; CREATE TABLE managers(
testdb(&gt; mgr_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('mgr_id_seq'),
testdb(&gt; mgr_name VARCHAR(50),
testdb(&gt; mgr_email VARCHAR(50)
testdb(&gt; );

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

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=&gt; INSERT INTO managers (mgr_name, mgr_email) VALUES('bob smith', 'bob@smith.com');
INSERT 0 1
testdb=&gt; INSERT INTO managers (mgr_name, mgr_email) VALUES('tom jones', 'tom@jones.com');
INSERT 0 1

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=&gt; select * from managers;
 mgr_id | mgr_name  |   mgr_email
--------+-----------+---------------
      1 | bob smith | bob@smith.com
      2 | tom jones | tom@jones.com
(2 rows)

testdb=&gt;

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:

Leave a Reply Cancel Reply