6.4 The User Interface

You have seen how to create a database cluster and how to create and remove databases, and it is time to learn about PostgreSQL's user interface. The standard front end that is included in the core distribution of PostgreSQL is called psql. psql is an interactive tool for communicating with a PostgreSQL database. A database accessed by psql need not be situated on the local machine because psql can even communicate with remote machines.

 bash-2.04$ createdb phpbook -e CREATE DATABASE "phpbook" CREATE DATABASE 

After creating the database, you can start psql to work with the database interactively. Therefore you simply have to pass the name of the database to psql:

 bash-2.04$ psql phpbook 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 phpbook=# 

If no error has occurred, you will be in an interactive shell where you can send queries to the server. To see if PostgreSQL works correctly, you can write a query that does nothing but compute the sum of 1+1:

 phpbook=# SELECT 1+1;  ?column? ----------         2 (1 row) 

Not surprisingly, the result returned by the database is 2.

Sometimes queries are longer than just one line. PostgreSQL will execute the string until the first semicolon is found. If you are in the middle of a command, the command prompt will look slightly different, as you can see in the following example:

 phpbook=# SELECT 1+ phpbook-# 1 phpbook-# ;  ?column? ----------         2 (1 row) 

After you have performed your first steps with the PostgreSQL interactive terminal, you will be guided through some features of psql that make this tool powerful and comfortable.

To find out more about psql, you can use \? to get a list of all commands provided by the shell:

 phpbook=# \ ?  \a             toggle between unaligned and aligned output mode  \c[onnect] [DBNAME|- [USER]]                 connect to new database (currently "phpbook")  \C TITLE       set table title  \cd [DIRNAME]  change the current working directory  \copy ...      perform SQL COPY with data stream to the client host  \copyright     show PostgreSQL usage and distribution terms  \d TABLE       describe table (or view, index, sequence)  \d{t|i|s|v}... list tables/indexes/sequences/views  \d{p|S|l}      list access privileges, system tables, or large objects  \da            list aggregate functions  \dd NAME       show comment for table, type, function, or operator  \df            list functions  \do            list operators  \dT            list data types  \e FILENAME    edit the current query buffer or file with external editor  \echo TEXT     write text to standard output  \encoding ENCODING  set client encoding  \f STRING      set field separator  \g FILENAME    send SQL command to server (and write results to file or |pipe)  \h NAME        help on syntax of SQL commands, * for all commands  \H             toggle HTML output mode (currently off)  \i FILENAME    execute commands from file  \l             list all databases  \lo_export, \lo_import, \lo_list, \lo_unlink                 large object operations  \o FILENAME    send all query results to file or |pipe  \p             show the content of the current query buffer  \pset VAR      set table output option (VAR := {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 FILENAME    print history or save it to file  \set NAME VALUE  set internal variable  \t             show only rows (currently off)  \T TEXT        set HTML table tag attributes  \unset NAME    unset (delete) internal variable  \w FILENAME    write current query buffer to file  \x             toggle expanded output (currently off)  \z             list table access privileges  \! [COMMAND]   execute command in shell or start interactive shell 

The list seems endless, but let's have a look at the most widely used commands.

Usually all commands executed by psql start with a backslash. This makes it easy to distinguish a built-in feature of psql from a command that has to be sent to the backend process, which is responsible for executing SQL code.

psql provides many useful commands for making daily life with the database easier. With the help of \d, for example, it is possible to list all relations a database contains:

 phpbook=# \ d No relations found. 

In this case no relations can be found because no tables have been defined yet. If \d is called with a table, a view, or any other object as the first parameter, the data structure of the table or view will be listed:

 phpbook=# \d pg_rules         View "pg_rules"    Column   | Type | Modifiers ------------+------+-----------  tablename  | name |  rulename   | name |  definition | text | View definition: SELECT c.relname AS tablename, r.rulename, pg_get_ruledef (r.rulename) AS definition FROM pg_rewrite r, pg_class c WHERE ((r.rulename !~ '^_RET'::text) AND (c.oid = r.ev_class)); 

pg_rules is a view consisting of three columns and is defined by the SQL command listed in the definition of the view. As you can see, finding the definition of a table or a view is an easy task when using PostgreSQL's onboard tools.

In addition, it is also possible to get a list of all data types, functions, or operators available in the current database.

Another command that is widely used is \h. With the help of \h, it is possible to retrieve information about the syntax of a SQL command. If you are using \h only, you will get a list of all commands available:

 phpbook=# \ h Available help:   ABORT                     CREATE TABLE AS           FETCH   ALTER GROUP               CREATE TRIGGER            GRANT   ALTER TABLE               CREATE TYPE               INSERT   ALTER USER                CREATE USER               LISTEN   ANALYZE                   CREATE VIEW               LOAD   BEGIN                     DECLARE                   LOCK   CHECKPOINT                DELETE                    MOVE   CLOSE                     DROP AGGREGATE            NOTIFY   CLUSTER                   DROP DATABASE             REINDEX   COMMENT                   DROP FUNCTION             RESET   COMMIT                    DROP GROUP                REVOKE   COPY                      DROP INDEX                ROLLBACK   CREATE AGGREGATE          DROP LANGUAGE             SELECT   CREATE CONSTRAINT TRIGGER DROP OPERATOR             SELECT INTO   CREATE DATABASE           DROP RULE                 SET   CREATE FUNCTION           DROP SEQUENCE             SET CONSTRAINTS   CREATE GROUP              DROP TABLE                SET SESSION AUTHORIZATION   CREATE INDEX              DROP TRIGGER              SET TRANSACTION   CREATE LANGUAGE           DROP TYPE                 SHOW   CREATE OPERATOR           DROP USER                 TRUNCATE   CREATE RULE               DROP VIEW                 UNLISTEN   CREATE SEQUENCE           END                       UPDATE   CREATE TABLE              EXPLAIN                   VACUUM 

If you need information about a specific SQL command, you can call \h and add the name of the command to it:

 phpbook=# \ h UPDATE Command:     UPDATE Description: update rows of a table Syntax: UPDATE [ ONLY ] table SET col = expression [, ...]     [ FROM fromlist ]     [ WHERE condition ] 

In this example you see an overview of the UPDATE commands syntax. The description shows what parts of the command are optional and which tokens are required by PostgreSQL. All words in brackets are optional and do not have to be passed to the server.

If you want PostgreSQL to return HTML code instead of ordinary ASCII code, you can use \H to turn HTML output on:

 phpbook=# \ H Output format is html. phpbook=# SELECT 1+1; <table border=1>   <tr>     <th align=center>?column?</th>   </tr>   <tr valign=top>     <td align=right>2</td>   </tr> </table> (1 row)<br> 

This time the result of SELECT 1+1; is returned as an HTML document. To turn off HTML again, you have to use \H again:

 phpbook=# \ H Output format is aligned. 

To generate a list of all databases located in the database cluster you are working on, you can use \l:

 phpbook=# \ l         List of databases    Name    |  Owner   | Encoding -----------+----------+-----------  phpbook   | postgres | SQL_ASCII  template0 | postgres | SQL_ASCII  template1 | postgres | SQL_ASCII (3 rows) 

You have already seen this output in the section called "Creating and Removing Databases" it is the same output you can also generate with the help of psql -l.

If you want to redirect the output generated by PostgreSQL to a file, \o will do the job for you. Just define a file and the output of all SQL commands sent to the server will be stored in the file:

 phpbook=# \ o /tmp/result.txt phpbook=# SELECT 1+1; 

The result of SELECT 1+1; will be stored in /tmp/result.txt. To make sure that this has happened, you can quit psql:

 phpbook=# \ q 

cat will list the content of the file on the screen:

 bash-2.04$ cat /tmp/result.txt  ?column? ----------         2 (1 row) 

psql is a powerful tool. In addition to the functions you have just seen, psql provides a variety of command-line parameters you can use to access PostgreSQL.

Let's have a look at the command-line parameters accepted by psql:

 bash-2.04$ 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 COMMAND      Run only single command (SQL or internal) and exit   -d DBNAME       Specify database name to connect to (default: postgres)   -e              Echo commands sent to server   -E              Display queries that internal commands generate   -f FILENAME     Execute commands from file, then exit   -F STRING       Set field separator (default: "|") (-P fieldsep=)   -h HOSTNAME     Specify database server host (default: local socket)   -H              HTML table output mode (-P format=html)   -l              List available databases, then exit   -n              Disable enhanced command line editing (readline)   -o FILENAME     Send query results to file (or |pipe)   -p PORT         Specify database server port (default: 5432)   -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 (end of line terminates SQL command)   -t              Print rows only (-P tuples_only)   -T TEXT         Set HTML table tag attributes (width, border) (-P tableattr=)   -U NAME         Specify database user name (default: postgres)   -v NAME=VALUE   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 documentation. Report bugs to <[e-mail]>. 

With the help of -c, it is possible to send a single command to the server. This is very useful when you are running PostgreSQL on a remote host or in batch mode. This way it is an easy task to use PostgreSQL's output in a shell script.

To tell psql which database has to be used, -d is provided. Depending on whether the database is located on the local machine or a remote host, you might have to use -h. With the help of -h, you can define a host psql should connect to.

If you want to connect to PostgreSQL with a different user, the default flag, -U, has to be used. Using this flag, you can connect to the database as any user known by the database. By default this is only the user called postgres.