PostgreSQL Tips and Tricks

# Log in to a postgreSQL database
psql -U username dbname

# Get list of databases.
SELECT datname FROM pg_database;
# or
# or
psql -l

# Get size of tables, sorted in descending order.
SELECT relname, relpages,reltuples FROM pg_class ORDER BY relpages DESC;

# Show tables in a database.
select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE';
# or

# Show structure of a table.
\d tablename

psql Commands


\c[onnect] [DBNAME|- [USER]]connect to new database (currently "dfdata")
\cd [DIR]change the current working directory
\copyrightshow PostgreSQL usage and distribution terms
\encoding [ENCODING]show or set client encoding
\h [NAME]help on syntax of SQL commands, * for all commands
\qquit psql
\set [NAME [VALUE]]set internal variable, or list all if no parameters
\timingtoggle timing of commands (currently off)
\unset NAMEunset (delete) internal variable
\! [COMMAND]execute command in shell or start interactive shell

Query Buffer

\e [FILE]edit the query buffer (or file) with external editor
\g [FILE]send query buffer to server (and results to file or pipe)
\pshow the contents of the query buffer
\rreset (clear) the query buffer
\s [FILE]display history or save it to file
\w FILEwrite query buffer to file


\echo [STRING]write string to standard output
\i FILEexecute commands from file
\o [FILE]send all query results to file or |pipe
\qecho [STRING]write string to query output stream (see \o)


\d [NAME]describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)list tables/indexes/sequences/views/system tables
\da [PATTERN]list aggregate functions
\db [PATTERN]list tablespaces (add "+" for more detail)
\dc [PATTERN]list conversions
\dClist casts
\dd [PATTERN]show comment for object
\dD [PATTERN]list domains
\df [PATTERN]list functions (add "+" for more detail)
\dg [PATTERN]list groups
\dn [PATTERN]list schemas (add "+" for more detail)
\do [NAME]list operators
\dllist large objects, same as \lo_list
\dp [PATTERN]list table, view, and sequence access privileges
\dT [PATTERN]list data types (add "+" for more detail)
\du [PATTERN]list users
\llist all databases (add "+" for more detail)
\z [PATTERN]list table, view, and sequence access privileges (same as \dp)


\atoggle between unaligned and aligned output mode
\C [STRING]set table title, or unset if none
\f [STRING]show or set field separator for unaligned query output
\Htoggle HTML output mode (currently off)
\pset NAME [VALUE]set table output option
(NAME := {format|border|expanded|fieldsep|footer|null| | |numericlocale|recordsep|tuples_only|title|tableattr|pager})
\tshow only rows (currently off)
\T [STRING]set HTML tag attributes, or unset if none
toggle expanded output (currently off)

Copy, Large Object

\copy ...perform SQL COPY with data stream to the client host
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_unlink LOBOIDlarge object operations


Simple expect ssh example

This expect script would be called from a shell script, and would ssh to the host passed as an argument (argv), perform the command specified, and disconnect. (Thanks, Tiger O.)


set timeout 1
set cmd {uname -a}

spawn ssh root@$argv
expect_after eof { exit 0 }

## interact with SSH
expect "yes/no" { send "yes\r" }
expect "password:" { send "rootpasswd\r" }

expect "# "
send "$cmd\r"
expect "$cmd\r"
expect "(.*)\r"
send "exit\r