New PostGIS Installer

You can now install PostGIS via cPanel.

Simply log into cPanel and click the PostGIS icon located in the “Databases” section.

Select the database and click the “Install PostGIS” button.

Auto Increment in PhpPgAdmin

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

Welcome to the AcuGIS Blog

Welcome to the AcuGIS Blog

Welcome to the new AcuGIS Blog.

Our new AcuGIS Blog is built with Apache Roller 5.0 and PostgreSQL 9.1.

We are just getting started the week of 02 January, 2012.

Please check back soon!

1 14 15 16
back to top
×