postgresql psql console command reference

http://www.postgresql.org/docs/8.4/static/app-psql.html

postgresql psql show database, table, column

SHOW TABLE:
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;

SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

RENAME DATABASE
postgresql: alter database old_name rename to new_name;

Solve the pg_dump version mismatch error

After updating postgresql, the server runs on the latest version, 9.2.0.
Whilst, pg_dump in /usr/bin remains on version 9.1.4.

To solve this problem. Remove /usr/bin/pg_dump and create symbolic link
to new pg_dump in /usr/local/bin/pg_dump.

i.e. $ sudo ln -s /usr/local/bin/pg_dump /usr/bin/pg_dump

Done.

Setup or update postgresql on Mac

Remove any previous version:
brew rm postgresql --force

Installation
brew update
brew install postgresql

Initialise a database.
initdb /usr/local/var/postgres

Set to start at login with (in one line below)
mkdir -p ~/Library/LaunchAgents
 

cp /usr/local/Cellar/postgresql/[x.x.x]/[....].postgres.plist ~/Library/LaunchAgents/


launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

OR

Add aliases to .bashrc to start and stop the server:
alias pgs='pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start'

alias pgq='pg_ctl -D /usr/local/var/postgres stop -s -m fast'

Postgresql client verification:
$which psql
$ls -al /usr/local/bin/psql 
/usr/local/bin/psql -> ../Cellar/postgresql/.../bin/psql

(Edit ~/.bash_profile : PATH=/usr/local/bin:$PATH)


Update postgresql tools versions
Tools like psql or pg_dump may not be update by the installations.
Just remove old version pg_* in /usr/bin, and create symbolic link to new ones in /usr/local/bin.