Using PostgreSQL

We are using the PostgreSQL database management system (which will often be referred to simply as "Postgres"). PostgreSQL is an open-source DBMS with a very long history of research and development.

This document provides information on using the PostgreSQL system and provides references to relevant documentation.

Note: we are currently running version 9.x of PostgreSQL; choose tools accordingly.

Creating a PostgreSQL Account

In order to access PostgreSQL, you will need to create an account with the DBMS itself. Using this form you can select a database username and password for PostgreSQL access. Please be aware that PostgreSQL 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 PostgreSQL account will continue to exist for any length of time beyond a given semester.

Getting Started with PostgreSQL

The PostgreSQL database server runs as a daemon process on a local Linux/UNIX server (e.g., dhansen.cs.georgefox.edu)

To access a PostgreSQL database you must either use an interactive tool or embedded-language interfaces - see the section on accessing PostgreSQL from application programs for more information on client tools you can install.

I have installed a web-based portal for accessing the databases on my server. You can log in using the database account you created per the instructions above.

PostgreSQL provides a command-line client interface - psql You can use this on most any system that has the PostgreSQL client tools installed. However, you may find one of the GUI-clients easier to use - see section on Useful Tools below.

Here is a quick tutorial on using the PostgreSQL DBMS via the command-line interface that should be sufficient to get you started. Note that the commands and SQL shown below can be entered into most any client, command-line or GUI.

The easiest way to access PostgreSQL databases is via the interactive SQL command-line client program called psql 

$ psql -U dhansen -h dhansen.cs.georgefox.edu company
at the command prompt (Note: you need to provide different values for the username, database name, and hostname depending on who you are and where the PostgreSQL server is running).

To learn the full syntax of the psql command, use the --help flag when you execute the program: 

$ psql --help
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "dhansen")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute command file as a single transaction
  --help                   show this help, then exit
  --version                output version information, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           set field separator (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           set record separator (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "dhansen")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (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.

Report bugs to <pgsql-bugs@postgresql.org>.

NOTE: I have activated the access-control mechanisms of PostgreSQL. You must provide a username using the -U switch which 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 PostgreSQL. You type SQL statements (use as many lines as you want), end them with either \g or a semi-colon (;), and PostgreSQL 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 available for PostgreSQL. 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.

As noted above, I have provided a server-side web-based portal for accessing my server. This portal provides the same sort of GUI access that many of these client-side tools provide.

Creating Databases

To create or destroy a database you issue "queries" to the database (e.g., from psql): 
company=> create database mydatabase;
company=> drop database mydatabase;
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).

Accessing PostgreSQL from Application Programs

A PostgreSQL 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 usable 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 a database.

JDBC Access

For an example of how to access PostgreSQL using JDBC, see this Java program. With both this program and the JDBC ".jar" file in the same directory (assuming you saved the jarfile as postgresql.jar and 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

or 
C:\> java -cp .;postgresql.jar JDBCExample
for Windows (note the use of the ';' character in the classpath)

When using Windows, you may also need to specify the entire path to the java and javac executables.

PHP Access

For an example of how to access PostgreSQL using PHP, run this PHP script whose source can be viewed here.

Note that these programs and scripts include a database username and password! Protection of source-code is vital when your code includes authentication information.

PostgreSQL Documentation

PostgreSQL documentation is available online.

In addition, many of the PostgreSQL commands are available as man pages: 

$ man createdb
and psql includes on-line help for relevant commands as well as SQL syntax via the \h command.

PostgreSQL Caveats and Bugs

PostgreSQL Data Model
The data model of PostgreSQL is an extended-relational or object-relational model that includes classes, inheritance, behavior, rules, etc. Since the data model is a super-set of the relational model, it should suffice for our purposes. Be aware that PostgreSQL documentation sometimes uses the term class instead of table or relation. For our purposes the terms are synonymous. For my courses, please do not use any of the PostgreSQL-specific modeling constructs (e.g., multi-valued attributes, inheritance, classes).

SQL Syntax & Semantics
PostgreSQL has recently been upgraded and now provides a close approximation to the SQL92 standard. The SQL documentation provided with PostgreSQL is quite good and is careful to note when PostgreSQL deviates from the SQL92 standard and which features are not yet implemented. For the most part, most SQL described in any text should be accepted by PostgreSQL.

Bugs
None that I am aware of

Last modified: , by David M. Hansen