Postgis – Page 12

May 12, 2012May 11, 2015

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.

February 16, 2012May 11, 2015

SVN and GIT

Subversion (SVN) and GIT are now enabled on all cPanel servers.

If you have questions, please raise a support ticket.

February 16, 2012May 11, 2015

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: