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 [email protected].
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 UserThe -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 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, 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.
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 |
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.