PostgreSQL- Basics

I'm compiling this post to create a reference which any postgreSQL-newbie (like myself) can use to get the basic things right !

You need a postgreSQL server up and running before moving further.

sudo apt-get install postgresql

1. Update postgres user password.In a terminal run following and give the password when prompted.

sudo -u postgres psql postgres
\password postgres

2.  Create Database with the name wso2bam. In a terminal run,

sudo -u postgres createdb wso2bam

3.  Connect to psql- the terminal based frontend,

sudo -u postgres psql postgres

 after you run the above command you will get a terminal with postgres=#.

root@thinkPad:~# sudo -u postgres psql postgres
psql (8.4.19)
Type "help" for help.

postgres=#

Note -: Here onwards I'm going to execute all commands within this psql terminal.

4. List down databases(similar to MySQL show databases.

\list

5. Select database wso2bam.

\c wso2bam

6. List tables within wso2bam current database.

\dt

If you are not interested in remembering and typing all these commands, pgAdmin3 might make your life easy. You can install this tool by running,

sudo apt-get install pgadmin3

You can use following official documentation to try out basic queries[1].

Please note that I have tested and verified above steps on a Ubuntu 12.10 machine, using  both PostgreSQL 8.4 and 9.1 servers.

[1]. http://www.postgresql.org/docs/8.4/static/tutorial-select.html