PostgreSQL Tips and Tricks


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

# Get list of databases.
SELECT datname FROM pg_database;
# or
\l
# 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
\dt

# Show structure of a table.
\d tablename

psql Commands

General

\c[onnect] [DBNAME|- [USER]]	connect to new database (currently "dfdata")
\cd [DIR]	change the current working directory
\copyright	show PostgreSQL usage and distribution terms
\encoding [ENCODING]	show or set client encoding
\h [NAME]	help on syntax of SQL commands, * for all commands
\q	quit psql
\set [NAME [VALUE]]	set internal variable, or list all if no parameters
\timing	toggle timing of commands (currently off)
\unset NAME	unset (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)
\p	show the contents of the query buffer
\r	reset (clear) the query buffer
\s [FILE]	display history or save it to file
\w FILE	write query buffer to file

Input/Output

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

Informational||

\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
\dC	list 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
\dl	list 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
\l	list all databases (add "+" for more detail)
\z [PATTERN]	list table, view, and sequence access privileges (same as \dp)

Formatting

\a	toggle 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
\H	toggle 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})
\t	show only rows (currently off)
\T [STRING]	set HTML tag attributes, or unset if none \x 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_list \lo_unlink LOBOID large 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.)

#!/usr/bin/expect

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