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