postgresql adventure: part 4, the graphical frontend

There is a graphical frontend for postgresql, the pgadmin. This is a frontend that pretty much do anything, and really depends that whether you have enough privilege to do certain task.

Here you can write procedure, create(and drop) database, create tables, create schema, privilege etc.

To create a table on your database. it is a matter select your database on the left side. then under the schema, which should be public, unless you created other schema. Then under it is a set of many thing, that I am unsure of.

You can create a table by right click on tables, and click new tables



And it shows a table editor. Which make life easier for gui oriented user.

To install pgadmin 3 on ubuntu.

It is.

apt-get install pgadmin3

postgresql adventure: part 3, a bit on the sql

Actually I did this sometime ago for my ruby on rails experiment. On my ubuntu machine

To start on creating the database, on postgres, as I said before, is very unlike mysql. In mysql, it is calling createdb on mysql shell. On postgres, you call on shell:

createdb databasename

if something happen and you want to drop it, it is done by calling

dropdb databasename

then you call

psql databasename

here i assume, that you create a user, in postgresql server that is your name. Suppose the above command doesn't doesn't work, do this, which I only tested on ubuntu, on other you might need to login as postgres user:

sudo -u postgres psql

now you can create tables, which again, very different from mysql. With many similarity as well.
in the psql shell, create table like this:

create table contact(
id serial primary key,
name varchar(255),
address text,
email varchar(255)
);

Which very similar with mysql, except, why id is serial?
Well, it turn out that, there is no autoincrement on postgresql, but it have a serial, which is really an autoincrementing integer. So that's interesting.

Actually this is just the very surface of postgresql sql. Actually, there is many features, that mysql don't have or have it very recently. such as
- inheritance, you can, inherit from another table, ala oop. mysql don't have that
- procedural language, which mysql, introduced stored procedure in mysql 5. not too long ago.

I barely touch this, so there is more adventure for me in the future

postgresql adventure: part 2, difference( from mysql)

One thing I learn from mysql is that. there is only one shell, that is the mysql program.

On the other hand, postgresql have a few program, that is, createuser, createdb, dropdb, among a few. Which is a bit different from mysql, but still, quite cool, nice feature that I like.

And another thing is that, postgresql have one frontend, which is the pgadmin, which is a nice thing to have.

postgresql adventure: part 1, usage and installation on gutsy

I'm actually, start from using mysql, and actually, learn mysql in class. Since i'm on my vacation, so i decide to try out postgresql. Which I qoute from their website, "the world most advance open source database".

So install I did. Like many open source software, it is available on ubuntu repository, can be found using synaptic.

unlike mysql, which only have one program, mysql, to run everything, postgress sql have a few. And actually need a few steps before you can actually start using the database.

1) Basically, first give password to postgres account, this is equivalent to root, on mysql. Also so that you can use it later for pgadmin, a graphical frontend for postgreSQL. Because you will need a password, to login, even you set no password as your password. psql, is equivalent to mysql, the shell to access the database.

sudo -u postgres psql

then type

alter user postgres with password 'your password';

type \q, to quit.

2) Sometime i am lazy, to type long, so i grant access to my own account, since this is a development machine, so I create a user account, as root, for the postgresql server.

So I type:

sudo -u postgres createuser yourname

then it will prompt a few things, just say yet.

Then it done