Connecting to a Database

Assuming that you have a copy of PostgreSQL up and running, it's pretty simple to connect to the database. Here is an example:

$ psql -d movies
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

movies=# q

The psql program is a text-based interface to a PostgreSQL database. When you are running psql, you won't see a graphical applicationno buttons or pictures or other bells and whistles, just a text-based interface. Later, I'll show you another client application that does provide a graphical interface (pgaccess).

psql supports a large collection of command-line options. To see a summary of the options that you can use, type psql --help:

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

Usage:
 psql [options] [dbname [username]]

Options:
 -a Echo all input from script
 -A Unaligned table output mode (-P format=unaligned)
 -c  Run only single query (or slash command) and exit
 -d  Specify database name to connect to (default: korry)
 -e Echo queries sent to backend
 -E Display queries that internal commands generate
 -f  Execute queries from file, then exit
 -F  Set field separator (default: "|") (-P fieldsep=)
 -h  Specify database server host (default: domain socket)
 -H HTML table output mode (-P format=html)
 -l List available databases, then exit
 -n Disable readline
 -o  Send query output to filename (or |pipe)
 -p  Specify database server port (default: hardwired)
 -P var[=arg] Set printing option 'var' to 'arg' (see pset command)
 -q Run quietly (no messages, only query output)
 -R  Set record separator (default: newline) (-P recordsep=)
 -s Single step mode (confirm each query)
 -S Single line mode (newline terminates query)
 -t Print rows only (-P tuples_only)
 -T text Set HTML table tag options (width, border) (-P tableattr=)
 -U  Specify database username (default: Administrator)
 -v name=val Set psql variable 'name' to 'value'
 -V Show version information and exit
 -W Prompt for password (should happen automatically)
 -x Turn on expanded table output (-P expanded)
 -X Do not read startup file (~/.psqlrc)

For more information, type ? (for internal commands) or help (for SQL commands) from within psql, or consult the psql section in the PostgreSQL manual, which accompanies the distribution and is also available at http://www.postgresql.org. Report bugs to [e-mail].

The most important options are -U , -d , -h , and -p .

The -U option allows you to specify a username other than the one you are logged in as. For example, let's say that you are logged in to your host as user bruce and you want to connect to a PostgreSQL database as user sheila. This psql command makes the connection (or at least tries to):

$ whoami
bruce
$ psql -U sheila -d movies

Impersonating Another User

The -U option may or may not allow you to impersonate another user. Depending on how your PostgreSQL administrator has configured database security, you might be prompted for sheila's password; if you don't know the proper password, you won't be allowed to impersonate her. (Chapter 23 discusses security in greater detail.) If you don't provide psql with a username, it will assume the username that you used when you logged in to your host.

You use the -d option to specify to which database you want to connect. If you don't specify a database, PostgreSQL will assume that you want to connect to a database whose name is your username. For example, if you are logged in as user bruce, PostgreSQL will assume that you want to connect to a database named bruce.

The -d and -U are not strictly required. The command line for psql should be of the following form:

psql [options] [dbname [username]]

If you are connecting to a PostgreSQL server that is running on the host that you are logged in to, you probably don't have to worry about the -h and -p options. If, on the other hand, you are connecting to a PostgreSQL server running on a different host, use the -h option to tell psql which host to connect to. You can also use the -p option to specify a TCP/IP port numberyou only have to do that if you are connecting to a server that uses a nonstandard port (PostgreSQL usually listens for client connections on TCP/IP port number 5432). Here are a few examples:

$ # connect to a server waiting on the default port on host 192.168.0.1
$ psql -h 192.168.0.1

$ # connect to a server waiting on port 2000 on host arturo
$ psql -h arturo -p 2000

If you prefer, you can specify the database name, hostname, and TCP/IP port number using environment variables rather than using the command-line options. Table 1.3 lists some of the psql command-line options and the corresponding environment variables.

Table 1.3. psql Environment Variables

Command-Line Option		Environment Variable Meaning
-d	PGDATABASE	Name of database to connect to
-h	PGHOST	Name of host to connect to
-p	PGPORT	Port number to connect to
-U	PGUSER	PostgreSQL Username

 

A (Very) Simple Query

At this point, you should be running the psql client application. Let's try a very simple query:

$ psql -d movies
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

movies=# SELECT user;
 current_user
---------------
 korry
(1 row)

movies=# q

$

Let's take a close look at this session. First, you can see that I started the psql program with the -d movies optionthis tells psql that I want to connect to the movies database.

After greeting me and providing me with a few crucial hints, psql issues a prompt: movies=#. psql encodes some useful information into the prompt, starting with the name of the database that I am currently connected to (movies in this case). The character that follows the database name can vary. A = character means that psql is waiting for me to start a command. A - character means that psql is waiting for me to complete a command (psql allows you to split a single command over multiple lines. The first line is prompted by a = character; subsequent lines are prompted by a - character). If the prompt ends with a ( character, you have entered more opening parentheses than closing parentheses.

You can see the command that I entered following the prompt: SELECT user;. Each SQL command starts with a verbin this case, SELECT. The verb tells PostgreSQL what you want to do and the rest of the command provides information specific to that command. I am executing a SELECT command. SELECT is used to retrieve information from the database. When you execute a SELECT command, you have to tell PostgreSQL what information you are interested in. I want to retrieve my PostgreSQL user ID so I SELECT user. The final part of this command is the semicolon (;)each SQL command must end with a semicolon.

After I enter the SELECT command (and press the Return key), psql displays the results of my command:

current_user
---------------
 korry
(1 row)

When you execute a SELECT command, psql starts by displaying a row of column headers. I have selected only a single column of information so I see only a single column header (each column header displays the name of the column). Following the row of column headers is a single row of separator characters (dashes). Next comes zero or more rows of the data that I requested. Finally, psql shows a count of the number of data rows displayed.

I ended this session using the q command.

table title copy ... perform SQL COPY with data stream to the client

machine copyright show PostgreSQL usage and distribution terms d 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 write text to stdout encoding set client encoding f 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 read and execute queries from 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 set table output = {format|border|expanded|fieldsep| null|recordsep|tuples_only|title|tableattr|pager} q quit psql qecho write text to query output stream (see o) reset (clear) the query buffer s [file] print history or save it in [file] set set internal variable show only rows (currently off) T HTML table tags unset unset (delete) internal variable w write current query buffer to a x toggle expanded output (currently off) z list table access permissions ! [cmd] shell escape or command movies=#
 

The most important meta-commands are ? (meta-command help), and q (quit). The h (SQL help) meta-command is also very useful. Notice that unlike SQL commands, meta-commands don't require a terminating semicolon, which means that meta-commands must be entered entirely on one line. In the next few sections, I'll show you some of the other meta-commands.