Summer Project: Setup PostgreSQL

Using my shiny new upgraded Ubuntu 9.04, I’ve gotten around to installing PostgreSQL.

Note: this is not a tutorial.

The installation was simple, as are most installations with Ubuntu. The initial “get going” was a bit odd for PostgreSQL though. The one thing you have to remember when initially trying to get things going is this…

sudo -u postgres [command]

In order to set things up I had to use the postgres user account.  Using this I was able to create a password for postgres, create my own user account with super user privileges and a new database.  Once I had created my own account and secured the postgres user for remote network connections I was ready to move on and do whatever I want.  For administration purposes I’m going to stick with phpPgAdmin (php application) and pgAdmin3 (gui application), both available from the Ubuntu repositories.

After sifting around the admin interfaces a bit and creating a few test tables I started noticing a lot of differences between MySQL and PostgreSQL.  I’m really not going to get into the battle of who’s better.  It’s far too complicated for one post – and quite frankly I can’t tell you, yet.

What I can say though is that PostgreSQL seems to be a lot more versatile with respects to data types. It also seems to be a lot more configurable when it comes down to security and permissions of databases, schemas, tables.

A MySQL database contains: tables, views, stored procedures, functions and triggers.

A PostgreSQL database contains: schemas and full text search configurations. Sounds a bit off, right? but the schemas contains: tables, views, sequences, functions, domains.  This split allows for multiple schemas within a database, and essentially a much cleaner name space than what you can do with MySQL.  You can also use these schemas in the database to allocate different permissions to different groups of tables.  So far, not bad.

But, what are all these other things?

If you don’t know what tables and views are, you probably shouldn’t be reading this.  You should probably be reading a primer on what a database is.

One of the first “HDIDT?” was ‘Where’s the auto increment for my column?”  PostgreSQL doesn’t support an automagic counter like MySQL does.  So, for my standard table structure with an automatically numbered ID column, I had to figure it out.  This quickly introduced me to sequences and how PostgreSQL does things.  Using the data type ‘SERIAL‘ when I create the table, PostgreSQL automatically created a sequence for me which acts the same as an auto_increment setting for MySQL.  The difference? Well, with PostgreSQL’s sequences I can configure how it counts.  I can set the start, limit, increment, and whether or not it can cycle (loop, not useful for unique keys). The default SERIAL behaviour is to start at 0, increment by 1 with a maximum of 9223372036854775807 and not cycle.  That’s pretty good for a unique primary key if you ask me. I can also have multiple sequences on a single table, whereas MySQL only allows for a single auto_increment column.

Functions, much like MySQL’s stored procedures and functions only there’s something special about PostgreSQL’s functions.  That special feature is that you can do them in a few different ways.  Internal functions – built in when you compile PostgreSQL – not something that I can see a lot of use for in every day use, but if you’re a C programmer and want to make your own – you can!  The real kick here is the support for Procedure Language (PL) with multiple interfaces.  PostgreSQL supports pgSQL (their own), Python, TCL and Perl for creation of functions.  I don’t know about you, but those are some pretty powerful languages.  I’m not going to go into these in any depth, but I’m sold already.

Domains are a special little thing.  Slightly confused by the name, a domain is a user defined data type.  This is best described by the PostgreSQL manual:

Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table’s constraint individually.

With domains there is an ability to control data formatting as well as types at the server level.  I can see this being useful for BOFH DBA’s.

Another major difference that I’ll have to wrap my head around is the number of different data types that PostgreSQL supports.  Unlike MySQL’s fairly standard set, PostgreSQL supports a pile more.  A pile of native, specialized data types.  Nerdy things like ipv4 and ipv6, geometrical shapes, and money.  That’s pretty hot.

So far, I guess I have to say I’m pretty pleased with the shape things are taking with my adventure into PostgreSQL land. Only more time will tell as to whether it’s really good.

Published by Darryl Clarke

random text goes here, i guess