The User Interface


The User Interface

PostgreSQL offers an extremely powerful and comfortable user interface called psql. psql is the frontend of the database. It can be used to connect to local or to remote databases and offers many additional functions. Unlike Oracle's SQL plus, psql offers a very comfortable readline interface, which means that the user can scroll back to previous commands simply by using the cursor keys. Let's take a closer look at the psql command-line flags first (you can get the list by using psql --help in your favorite shell):

 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: postgres)   -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: postgres)   -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 <pgsql-bugs@postgresql.org>. 

You can see that psql is indeed very powerful and flexible. Here is an example of how you can use these flags:

 psql yourdb -h 192.168.1.2 -p 5432 

This command establishes a connection to the database yourdb stored on 192.168.1.2 using port 5432 . If the postmaster on 192.168.1.2 is correctly configured, you have successfully connected to the database.

psql opens the door to a huge pool of information and online help. Information about syntax or datatypes can easily be found by using the \? command. Here is a complete list of all available commands:

 \a                             toggle between unaligned and aligned mode  \c[onnect] [dbname- [user]]   connect to new database (currently 'xy')  \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 datatypes  \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,       large object operations   \lo_list, \lo_unlink  \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> = { formatborderexpandedfieldsepnullrecordseptuples_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 

You can easily imagine that all the information that can be accessed with the simple psql help commands makes daily life easier. Imagine that you would have to check your documentation every time you want to know the correct syntax of an SQL command. psql does the job for you. Learn the most important commands in this list, because they will save you a lot of time.



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net