Chapter 4. Applying SQL with PostgreSQL
Like most network-capable database servers, PostgreSQL fits very strongly into a client-server paradigm. The heart of PostgreSQL is the server back-end process named postmaster. It is called a "back-end" because it is not meant to be interfaced with directly by a user.
When you start the PostgreSQL service, the postmaster process starts running in the background, listening to a specific TCP/IP port for connections from clients. Unless explicitly configured, postmaster will bind, or listen, on port 5432.
There are several interfaces available for client connectivity to the postmaster. For our examples, this book will rely on the most portable and readily accessed client distributed with PostgreSQL, which is psql.
Introduction to psql
Sometimes called the interactive monitor, or interactive terminal, psql is the command-line client distributed with PostgreSQL. With psql, you have a simple but powerful tool with which you can directly interface with the PostgreSQL server and begin exploring SQL through PostgreSQL.
Starting psql
To start psql, you may want to first be sure that you have either copied the psql binary into a path which is in your system PATH variable (e.g., /usr/bin), or that you have placed the PostgreSQL binary path (e.g., /usr/local/pgsql/bin) within your list of paths in your PATH environment variable.
How to set the appropriate PATH variable will depend on your system shell. An example in either bash or ksh might read:
export PATH=$PATH:/usr/local/pgsql/bin
An example in either csh or tcsh might read:
set path=($path /usr/local/pgsql/bin)
Example 4-1. Setting System Path for psql
[user@host user]$ psql
bash: psql: command not found
[user@host user]$ echo $PATH
/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin
[user@host user]$ export PATH=$PATH:/usr/local/pgsql/bin
[user@host user]$ psql testdb
Welcome to psql, 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
testdb=#Note that Example 4-1 is demonstrated within a Bash shell.
Once you have appropriately set your PATH variable, you should be able to simply type psql at the command line to start up PostgreSQL interactive terminal.
 | Lifespan of Environment Variables |
|---|---|
Remember that shell environment variables, in general, are erased after you have logged out! If you wish for your changes to the PATH variable to be persistent on logging in, you will need to be sure to put the appropriate PATH declaration into your shell-specific start-up scripts (e.g., ~/.bash_profile). |
Introduction to psql Syntax
When you first start psql, you will be greeted with a brief synopsis of four essential psql slash commands: \h for SQL help, \? for help on psql-specific commands, \g for executing queries and \q for actually exiting psql once you are done.
Every psql-specific command is prefixed by a backslash; hence term "slash command" used above. For a complete list of slash commands and a brief description of each of their functions, type \? into the psql command line, and press enter.
Example 4-2. Listing psql Slash Commands
booktown=# \?
\a toggle between unaligned and aligned mode
\c[onnect] [dbname|- [user]]
connect to new database (currently 'booktown')
\C <title> table title
\copy ... perform SQL COPY with data stream to the client machine
\copyright show PostgreSQL usage and distribution terms
\d <table> describe table (or view, index, sequence)
\d{t|i|s|v} list tables/indices/sequences/views
\d{p|S|l} list permissions/system tables/lobjects
\da list aggregates
\dd [object] list comment for table, type, function, or operator
\df list functions
\do list operators
\dT list data types
\e [file] edit the current query buffer or [file] with external editor
\echo <text> write text to stdout
\encoding <encoding> set client encoding
\f <sep> change field separator
\g [file] send query to backend (and results in [file] or |pipe)
\h [cmd] help on syntax of sql commands, * for all commands
\H toggle HTML mode (currently off)
\i <file> read and execute queries from <file>
\l list all databases
\lo_export, \lo_import, \lo_list, \lo_unlink
large object operations
\o [file] send all query results to [file], or |pipe
\p show the content of the current query buffer
\pset <opt> set table output <opt> = {format|border|expanded|fieldsep|
null|recordsep|tuples_only|title|tableattr|pager}
\q quit psql
\qecho <text> write text to query output stream (see \o)
\r reset (clear) the query buffer
\s [file] print history or save it in [file]
\set <var> <value> set internal variable
\t show only rows (currently off)
\T <tags> HTML table tags
\unset <var> unset (delete) internal variable
\w <file> write current query buffer to a <file>
\x toggle expanded output (currently off)
\z list table access permissions
\! [cmd] shell escape or command
booktown=#Executing Queries
Entering and executing queries within psql can be done one of a few different ways. When using the client in interactive mode you will probably be directly entering query data (i.e., standard input, or stdin). However, through the use of psql's \i slash command you are able to let psql read and interpret a file on your local filesystem as the query data.
Entering queries at the psql prompt
To enter queries directly into the prompt, open psql and make sure you are connected to the correct database (and logged in as the correct user). You should be presented with a prompt that, by default, is set to display the name of the database you are currently connected to. The prompt should look something like the following immediately after opening psql:
Example 4-3. The psql Prompt
Welcome to psql, 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
testdb=# To pass SQL statements to PostgreSQL, just type them into the prompt. Anything you type (barring a slash command) will be queued until you terminate the query with a semi-colon, even if you start a new line. This allows you to spread query statements across multiple lines. Examine the following example to see how this is done.
Example 4-4. Entering Statements into psql
testdb=# SELECT * FROM employees testdb-# WHERE firstname = 'Michael';
This will return a table of matching data. This query could be broken up over multiple lines to improve readability even more and psql still would not send it until the terminating semi-colon was sent. What isn't displayed by this example is that the prompt will show you any end-character that you've left open, such as parenthesis. If you were to issue a CREATE TABLE command to start a statement, then hit enter to start a new line for readability purposes, you would see a prompt similar to what is displayed in the following example.
At this point you could continue the statement. The psql prompt is informing you of the open parenthesis by inserting an open parenthesis symbol into the prompt.
Editing the query buffer
Use the \e command to edit the current query buffer. Doing so can be extremely useful when entering queries and statements in psql, as you can easily go view and mofify all lines of your query or statement before it is committed.
 | Set the EDITOR Variable |
|---|---|
Before using this command, be sure that the EDITOR variable is set to the editor you wish to use, otherwise psql will use vi to edit your buffer. |
You can also use this command to save your current buffer as a file. The syntax of it is essentially the same, either way: issue the \e command to enter editing mode. This will open your editor and load the buffer as if it were a file. Complete whatever work you wish to do with the buffer, then use your editor's save function to save the buffer and return to psql. To save the query as a normal file, use your editor's save-as function to save it as a file other than the .tmp that it's opened as.