PostgreSQL Quickstart Guide

Updated on Fri, 07/31/2009 - 10:20am

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.1.9, is installed on cisdb.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 the vulcans or the ugrad "coffee" machines
From Perl using DBI and DBD::Pg. This is available on the vulcans, 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 cisdb.cis.uab.edu from outside the department firewall. This may change in the future. However, you can ssh into the department through the usual methods, and thus work remotely.

Below are quickstart instructions for each of these methods.

Using psql to connect

On the vulcans, psql is available in the default command path:

# psql -h cisdb

This will connect as the current user to a database of the same name as the current user on the host cisdb. 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 cisdb. 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 cisdb -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 on the vulcans using default perl (/usr/bin/perl) or as a CGI on the department web server which should use /usr/bin/perl as the perl path.

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=cisdb",
                       "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.

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).