CIS PostgreSQL Quickstart Guide

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, SunOS, Tru64), BeOS, and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

The PostgreSQL home page and documentation are available at http://www.postgresql.org.

The PostgreSQL server, version 8.0.3, is installed on vulcan.cis.uab.edu. You will be issued an account on this system if you are in a course which requires a database account. You have the following options for connecting to the server:

  • Using the 'psql' command-line utility, which is available on vulcan or the blazers.
  • From Perl using DBI and DBD::Pg. This is available on vulcan or the blazers, or from the department web server from a CGI script.
  • From PHP using PHP's pgsql extension. PHP is available on the department web server.
At this time, you cannot reach vulcan.cis.uab.edu from outside the department firewall. This may change in the future. However, you can ssh to the blazers or vulcan from outside the department, and thus work remotely.

Below are quickstart instructions for each of these methods.

Using psql to connect

On vulcan, psql is available at /usr/bin/psql. On the blazers it is available at /hf/sol8/pgsql/bin/psql.
# psql -h vulcan

This will connect as the current user to a database of the same name as the current user on the host vulcan. Thus, if you are logged in as joeuser, this will try to connect to a database named joeuser as the user joeuser on the host vulcan. If an account exists for joeuser and the database joeuser exists and the connecting host has rights to connect to the database as joeuser, you will receive a Password: prompt. Upon entering the correct password, you will then be presented with the psql command line.

Once on the psql command line, you can issue SQL queries to set up your tables, populate them with data, and make queries against your data. Here is a sample session.

# psql -h vulcan -U testuser
Password:
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
testuser=> create table test (testfield text);
CREATE TABLE
testuser=> \d test
     Table "public.test"
  Column   | Type | Modifiers
-----------+------+-----------
 testfield | text |

testuser=> insert into test values ('This is a test');
INSERT 17384 1
testuser=> select * from test;
   testfield
----------------
 This is a test
(1 row)
 
testuser=> delete from test where testfield like 'This%';
DELETE 1
testuser=> select * from test;
 testfield
-----------
(0 rows)
 
testuser=> drop table test;
DROP TABLE
testuser=> \q
#

Using Perl to Connect

Perl's DBD::Pg is available at the following locations: On vulcan using default perl (/usr/bin/perl), on the blazers at /usr/local/bin/perl (which is a link to /hf/sol8/bin/perl) or as a CGI on the department web server which should use /usr/bin/perl.

You can use Perl's DBI database-independent abstraction layer to connect to PostgreSQL. Below is a simple example (not to be considered robust code). For more information about using DBI, type 'perldoc DBI' at the command line.

#!/usr/bin/perl
use DBI;
my $dbh = DBI->connect("DBI:Pg:dbname=testuser;host=vulcan",
                       "testuser",
                       "PASSWORD") || die;
my $sth = $dbh->prepare("CREATE TABLE test (testfield text)");
$sth->execute;
$sth = $dbh->prepare("INSERT INTO test VALUES ('This is a test')");
$sth->execute;
$sth = $dbh->prepare("INSERT INTO test VALUES ('Another test')");
$sth->execute;
$sth = $dbh->prepare("SELECT * FROM test");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
  print "This row's testfield column has a value of: $row->{testfield}\n";
}

Using PHP to Connect

The PHP on our department web server supports PostgreSQL. Here is a very simple example PHP file. For more information about PHP's PostgreSQL interface, see http://www.php.net/manual/en/ref.pgsql.php.

<?php
   $database = pg_connect("host=vulcan user=testuser password=XXXXXXXX dbname=testuser");
   pg_query($database, "create table foo (bar int)");
?>

Seeing PHP Errors in the Web Browser

By default, the web server will not display PHP errors in the browser. You can override this behavior if you wish with an .htaccess configuration file. To do so create a directory for your PHP project, and in that directory create a file called .htaccess, with contents:

php_flag display_errors on
Now, PHP will display errors in the browser for PHP code that lives in that directory (and any subdirectories).
Dept. of Computer and Information Sciences   The University of Alabama at Birmingham
115A Campbell Hall, 1300 University Boulevard, Birmingham, Alabama 35294-1170
Phone: 205.934.2213 * Fax: 205.934.5473
Disclaimer for official UAB web pages