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 application ”no 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 <query>      Run only single query (or slash command) and exit   -d <dbname>     Specify database name to connect to (default: korry)   -e              Echo queries sent to backend   -E              Display queries that internal commands generate   -f <filename>   Execute queries from file, then exit   -F <string>     Set field separator (default: "") (-P fieldsep=)   -h <host>       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 <filename>   Send query output to filename (or pipe)   -p <port>       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 <string>     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 <username>   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 < user > , “d <dbname> , “h <host> , and “p <port> .

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 21, "Security," 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 number ”you 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, host name, 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 <dbname>	PGDATABASE	Name of database to connect to
-h <host>	PGHOST	Name of host to connect to
-p <port>	PGPORT	Port number to connect to
-U <user>	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 option ”this 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 verb ”in 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.

Tips for Interacting with PostgreSQL

The psql client has a lot of features that will make your PostgreSQL life easier.

Besides PostgreSQL commands ( SELECT , INSERT , UPDATE , CREATE TABLE , and so on), psql provides a number of internal commands (also known as meta-commands ). PostgreSQL commands are sent to the server, meta-commands are processed by psql itself. A meta-command begins with a backslash character ( \ ). You can obtain a list of all the meta-commands using the \? meta-command:

 movies=# \?  \a             toggle between unaligned and aligned mode  \c[onnect] [dbname- [user]]                 connect to new database (currently 'movies')  \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{tisv}    list tables/indices/sequences/views  \d{pSl}      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> = {formatborderexpandedfieldsep  nullrecordseptuples_onlytitletableattrpager}  \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 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.