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

 

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.

Step 3. Insert Data.

 

Step 4. Select on Table to View Sequence.

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

four × 1 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.