PostgreSQL commands

Start postgresql
=> /etc/init.d/postgresql start

Now we will connect to the database server as postgres user and create a database and a user with permission to access the database.
=> su - postgres
createdb joetestdb
psql joetestdb

To drop a user account:-
=> DROP USER jonathan;

DROP DATABASE -- remove a database:-
=> DROP DATABASE [ IF EXISTS ] name

SQL Dump :-
=> pg_dump dbname > outfile

Restoring the dump :-
=> psql dbname < infile

Using pg_dumpall (This mechanism is cumbersome and inappropriate when backing up an entire database cluster):-
=> pg_dumpall > outfile

The resulting dump can be restored with psql:-
=> psql -f infile postgres

=> To see a complete list of PostgreSQL (psql) commands, type \? at the SQL prompt.

Change the current working directory :-
=> \cd directory
=> \cd /opt/TBSM (To change to the /opt/TBSM directory)

Describes the structure of the table, sequence, or view that you specify :-
=> \d name
=> \d tickets

Lists help on the specified SQL command :-
=> \h SQL command
=> \h select , To view help for the select command

Quits the SQL session:-
=> \q

Lists the buffer history or saves it to the specified file :-
=> \s file
=> \s myfile.txt

Sets an internal variable or lists all variables if you specify no parameters :-
=> \set name value
=> \set High 4 , To set the High variable to 4

Writes the query buffer to the specified file:-
=> \w file
=> \w myfile.txt , To save the buffer to the myfile.txt file

Enter into postgreSQL:-
=> su - postgres

Create database:-
=> createdb joetestdb

Choose Database:-
=> psql joetestdb

Grant Privileges:-
=> CREATE USER joe WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE joetestdb TO joe;

view the tables in the current database:
=> \dt

How to find the largest table in the postgreSQL database?
=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

If you want only the first biggest table in the postgres database then append the above query with limit as:
=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;

relname – name of the relation/table.
relpages - relation pages ( number of pages, by default a page is 8kb )
pg_class – system table, which maintains the details of relations
limit 1 – limits the output to display only one row.

How to calculate postgreSQL database size in disk ?
=> SELECT pg_database_size('geekdb');

If you want it to be shown pretty, then use pg_size_pretty function which converts the size in bytes to human understandable format.
=> SELECT pg_size_pretty(pg_database_size('geekdb'));

How to calculate postgreSQL table size in disk ?
=> SELECT pg_size_pretty(pg_total_relation_size('big_table'));

How to find size of the postgreSQL table ( not including index ) ?
=> SELECT pg_size_pretty(pg_relation_size('big_table'));

How to view the indexes of an existing postgreSQL table ?
=> Syntax: # \d table_name


Apart from select statements you can use the following commands on psql prompt -

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

Enjoy :-)