PostgreSQL is an "object-relational database management system" available for many platforms like linux, windows, and MAC OS. It implements majority of SQL: 2008 standard. Many linux distributions have POSTGRESQL available in packages supplied. I got a hands on experience of working with POSTGRES 9.1 on my ubuntu 11.10 too.

     Installation of POSTGRES on Ubuntu 11.10: 

• Type sudo apt-get install postgresql (apt-get for ubuntu users). It takes a few minutes to install the package

• Now take your terminal again and type sudo su postgres (enter superuser password). This takes you inside postgres now type "psql" in the terminal.This will show you the postgres prompt "postgres#".

• Now you can start by creating user for postgres by sudo -u postgres createuser "username". This will ask "shall the new role be a superuser?(y/n)" -type y.

• Now create a database by using createdb "databasename". Type psql <space>databasename and the prompt enters the database.

• Now you can start creating tables using simple SQL queries.

Problems faced while installing!

  While installing I faced trouble connecting the database. The database requires setting password which can be done away by modifying the /etc/postgresql/9.1/main/postgresql.conf. Change #localhost  to localhost save and exit.

   When i started using postgres I faced an issue "FATAL: Peer authentication failed for user "username"".

This can be corrected by modifying the /etc/postgresql/9.1/main/pg_hba.conf file.

Here in the file change the last line for "localhost" with "trust" instead of "peer". Save the file and then try again! This is done so that we can avoid request for password each time we access postgres.

Now after successful completion of installation POSTGRES, when we type 

sudo su postgres  on your terminal, you are able to enter postgres followed by psql to get the postgres prompt.

You can start by creating tables and inserting data into it using SQL queries. Use SELECT * to view the contents of the table.

Installing psycopg2:

To install psycopg2, in your linux distribution search in the synaptic package for psycopg2 and install. If you do not find the package it can be readily downloaded from http://wiki.postgresql.org/wiki/psycopg. Save the file and build the setup. 

After the installation write a python code and import psycopg2 module. 

Python is a favored language to use with postgresql.

I am including the code I wrote to create a student record using Psycopg and create table in postgres.

The python code:

import psycopg2
try:
        conn = psycopg2.connect("dbname='template' user='anjali' host='localhost' password='asdfgh'")
except:
        print "i am unable to connect to the database"
cur = conn.cursor()
cur.execute("CREATE TABLE stud_rec(name char(40), marks integer);")
cur.execute("INSERT INTO stud_rec values('anjali', 50);")
cur.execute("INSERT INTO stud_rec values('aparna', 40);")
cur.execute("SELECT * FROM stud_rec;")
conn.commit()
cur.close()
conn.close()