A recent experience with postgreSQL forced me to get to know some of the syntax and associated architecture associated with a postegreSQL (w/ PHP) environment. I’m documenting a few tips which would have been helpful, Google is great, no question, but postgreSQL is not the most documented… so here’s my newb tips.

First Things First – XAMPP phpPgAdmin default Username

I setup postgreSQL in XAMPP, specifically, I set it up so phpPgAdmin was available. My first problem… how do I log in to phpPgAdmin? ThepostgreSQL install forces you to create a password, but what’s the username?  Shocker… “postgres”.

Table Naming Syntax

I’m a big fan of camel case, e.g. tableName. Don’t do it! Keep it all lower case.

phpPgAdmin – User Permissions

I setup a postgreSQL database for a client on a HostGator VM and ran in to some permission issues when trying to make a database (PHP) connection. If you’re used to cPanel and MySQL there is one major difference…when you’re using MySQL the user identity in your connection script is the user identity you setup, and assigned, in cPanel. With postgreSQL, if you log in to phpPgAdmin with your root user (which you normally do) you’ll have to use those same permissions (user/pass) in your connection script. Basically the user you setup using the database setup screen is, well, useless (and you can’t assign permissions). Likely this is different if you setup your db and users using SSH, but I can’t be sure. Just a tip…

Inserting a Record into postgreSQL with Auto Increment

I’m so used to just assigning “auto increment” to the primary key column in phpMyAdmin that I was baffled that there’s no equivalent in phpPgAdmin. (Apparently there is a type called “SERIAL”, but it wasn’t available in my version.) So here’s the newb tip… you need to setup a “sequence” table first which will get the job done. Here’s an example:

CREATE SEQUENCE article_seq;
CREATE TABLE articles(id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('article_seq'),title TEXT,date DATE,summary TEXT,updated DATE);

Running this in the “SQL” view of the database screen in phpPgAdmin will basically setup one visible table (“Articles”) and auto increment will be in effect when inserting. Who knew?

Select Distinct Year for Archive List

My last tip… trying to create an “archive” type list which will go through a table and pull all the distinct years which exist in a table where the column type is date:

$archiveSQL = "SELECT DISTINCT date_part('year', date_column) AS theyear FROM tbl_articles";
$rsArchive = pg_query($archiveSQL);
$output = '<ul>';
while($row_rsArchive = pg_fetch_assoc($rsArchive))
{
  $output .= '<li><a href="archive.php?year='.$row_rsArchive['theyear'].'">'.$row_rsArchive['theyear'].'</a></li>';
}
$output .= </ul>
echo $output;

That’s all I’ve got. Hope it’s helpful to other postgreSQL starters.