PostgreSQL study notes – psql

Features

1. non-interactive usage
2. command history (up/down arrow)
3. tab completion (sans Windows)
4. commands terminate with semi-colon and can wrap lines
5. defaults to supplying currently logged-in username as pg use

Tasks

1. explore ‘psql’
   a. ‘psql –version’ returns version of postgresql client
   b. ‘psql -l -U postgres’ lists installed db’s then exits.
   postgreSQL installs 3 default db’s
   1. ‘postgres’ – management db. contains user accounts, global settings, etc
   2. ‘template0’ – vanilla read-only db
   3. ‘template1’ – changable copy of template0, used as template for new db’s
   c. ‘psql’ with no options enters interactive mode (duh)
   a hash-mark ending the interactive-prompt denotes a ‘superuser’ eg: ‘postgres=#’
   d. ‘\h’ – returns sql-specific help eg: ‘ALTER TABLE …’
   ‘\h create’ filters above to just ‘CREATE …’ commands
   e. ‘\?’ – returns ‘psql’ specific help eg: ‘\?’ ie: usable metasequences ie shortcuts
   f. ‘\l’ – returns list of DBs. ‘\l+’ additionally returns DB sizes
   g. ‘\du[+]’ – returns list of users with access to postgresql.
   h. ‘\!’ open shell from session (‘exit’ from shell = back to psql)
   i. ‘\! [command] – runs command in shell non-interactively and returns to psql.
   j. ‘\i filename’ – execute command(s) in the file ie psql or sql commands
   k, multiple commands can be run on one line. Separate with space, terminate with semi-colon (eg: ‘\l \du;’)
   L: ‘\c’ – connect to another database or host eg: ‘\c template1’
   m. ‘\d’ list tables\views etc in current DB, ‘\dS’ list system tables, ‘\dS+’ list system tables with sizes.
   n, \q quit
2. c. ‘psql –help’ (or ‘psql -?’) psql option switches & defaults (short version then long version) eg: -U (username – short version), -l (list – short version), –version (long version) …