Unlike PhpMySQL, PhpPgAdmin does not feature a nice drop-down to auto increment on a column.
This post will demonstrate how to auto increment on a column in PostgreSQL using either the PhpPgAdmin SQL Editor as well as how to make use of PhpPgAdmin GUI to do some of the work for us.
The steps for using the PhpPgAdmin SQL Editor can be applied to using the command line (psql) as well.
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.
Let’s start by using the command line. You can use below for the PhpPgAdmin SQL Editor as well.
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.
Now, let’s take a look at how we can do this using some of the PhpPgAdmin GUI tools.
Using the PhpPgAdmin GUI Tools
Step 1: Create Sequence in PhpPgAdmin
Create your sequence using by selecting the Sequence node in PhpPgAdmin and then click “Create Sequence”
Enter the sequence name as shown below.
With your sequence created, you can now 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
