We are using the PostgreSQL database management system (which will often be referred to simply as "Postgres"). Postgres is an open-source DBMS with a very long history of research and development.
This document provides information on using the Postgres system and provides references to relevant documentation.
Note: we are currently running version 9.x of PostgreSQL; choose tools accordingly.
Creating a Postgres Account
In order to access Postgres, you will need to create an account with the
DBMS itself. Using this form you can
select a database username and password for Postgres access. Please be
aware that Postgres accounts are generally valid for particular classes
only and may deleted at the end of any given semester
without additional notice. In other words, do not assume your Postgres
account will continue to exist for any length of time beyond a given
Getting Started with Postgres
The Postgres database server runs as a daemon process on a local
Linux/UNIX server (e.g., cs.georgefox.edu)
To access a Postgres database you must either use an interactive tool or embedded-language interfaces - see the section on accessing Postgres from application programs for more information.
The command-line interface provided by Postgres is the psql program. You can use this on the CS box or most any *NIX system (including Cygwin). However, you may find one of the GUI-clients easier to use - see section on Usefull Tools below.
Here is a quick tutorial on using the Postgres DBMS that should be sufficient to get you started.
Postgres, and its associated tools, is included as part of many Linux distributions. Front-end and development tools are also available for Win9x/NT platforms.
The easiest way to access Postgres databases is via the interactive SQL client software called psql (i.e., typing psql -U dhansen -h cs.georgefox.edu ladder at the command prompt (Note: you need to provide different valuus for the username, database name, and hostname depending on who you are and where the Postgres server is running).
To learn the full syntax of the psql command, use the --help flag when you execute the program:
% psql --help This is psql 8.1.9, the PostgreSQL interactive terminal. Usage: psql [OPTIONS]... [DBNAME [USERNAME]] General options: -d DBNAME specify database name to connect to (default: "your username") -c COMMAND run only single command (SQL or internal) and exit -f FILENAME execute commands from file, then exit -l list available databases, then exit -v NAME=VALUE set psql variable NAME to VALUE -X do not read startup file (~/.psqlrc) --help show this help, then exit --version output version information, then exit Input and output options: -a echo all input from script -e echo commands sent to server -E display queries that internal commands generate -q run quietly (no messages, only query output) -o FILENAME send query results to file (or |pipe) -n disable enhanced command line editing (readline) -s single-step mode (confirm each query) -S single-line mode (end of line terminates SQL command) -L FILENAME send session log to file Output format options: -A unaligned table output mode (-P format=unaligned) -H HTML table output mode (-P format=html) -t print rows only (-P tuples_only) -T TEXT set HTML table tag attributes (width, border) (-P tableattr=) -x turn on expanded table output (-P expanded) -P VAR[=ARG] set printing option VAR to ARG (see \pset command) -F STRING set field separator (default: "|") (-P fieldsep=) -R STRING set record separator (default: newline) (-P recordsep=) Connection options: -h HOSTNAME database server host or socket directory (default: "local socket") -p PORT database server port (default: "5432") -U NAME database user name (default: "your username") -W prompt for password (should happen automatically) For more information, type "\?" (for internal commands) or "\help" (for SQL commands) from within psql, or consult the psql section in the PostgreSQL documentation.
NOTE: I have activated the access-control mechanisms of Postgres. You must provide a username using the -U switch wich tells psql to prompt you for a password for authorization.
To access a database, run psql as demonstrated here.
psql is a simple, interactive SQL front-end to Postgres. You type SQL statements (use as many lines as you want), end them with either \g or a semi-colon (;), and Postgres executes the query and the results are displayed by psql.
To quit a psql session type \q.
You can get help on psql commands with the \? command, and you can get help on SQL by typing \h.
Downloading Useful Tools
There are a variety of clients availalbe for Postgres.
Here is a list of GUI tools. Some, such as the SQL Workbench, will require you
to download the Java JDBC driver as well - see below.
To create or destroy a database you issue "queries" to the database
(e.g., from psql):
Note that you can not destroy a database you're currently working with. You can use the \c dbname command to change the database you are working with (you can always set your current database to the pre-existing template1 database initially, until you create your own database).company=> create database mydatabase; company=> drop database mydatabase;
Accessing Postgres from Application Programs
A Postgres database can be accessed by programs written in a number
of languages including C/C++, Java, PHP, PERL, etc. An
ODBC (open-database connectivity) interface is also
available. The ODBC and JDBC
interfaces should be useable from a variety of computer systems.
Below are links to relevant resources. These interfaces may prove
useful to you for implementing application programs to load and access
postgresql.jarand that it is located in the same directory as
JDBCExample.java), you should be able to execute this program with:
java -cp .:postgresql.jar JDBCExample
java -cp .;postgresql.jar JDBCExamplefor Windows (note the use of the ';' character in the classpath)
Note that these programs and scripts include a database username and password! Protection of source-code is vital when your code includes authentication information.
Postgres documentation is available
In addition, many of the Postgres commands are available as man pages:
and psql includes on-line help for relevant commands as well as SQL syntax via the% man createdb
Postgres Caveats and Bugs
Last modified: , by David M. Hansen