learn postgresql

My knowledge of PostgreSQL is now where I want it to be, and I feel comfortable running it. I’m currently using Postgres as a production database, and haven’t had any problems so far.

Am I a master? Would I put postgres on my resume? Uhh… HELL NO. Ruby on Rails has gotten me away from database-centric web development, so I haven’t even written a stored procedure yet.

However, I do know enough to setup an automatic backup system for my database. I have cron run pg_dump daily, which will create the backup file with all the SQL INSERT statements to restore my tables. Then, cron will version the file in my Subversion repository. If my database goes haywire, the most I lose is a day’s worth of data.

I’ve investigated PostgreSQL vs MySQL around Dec. 2006-Jan 2007. My conclusion was that PostgreSQL was superior to MySQL in architecture, feature set and reliability, and, back then, slightly inferior in speed, and very much behind in the number of users, but with great support via IRC (#postgresql on freenode.net).

When developing applications, I think that architecture comes first, and optimizations later. I prefer a solidly built piece of software that runs a little slower, to a less solid one that runs a bit faster. That said, PostgreSQL’s performance has increased over the years, and the difference to MySQL became minimal; nothing that another machine, Gig of RAM, hard disk RPM, or bunch of Megahertz couldn’t solve.

I’ve hand-picked at http://del.icio.us/dandvd/mysql+postgresql several online resources that compare MySQL to PostgreSQL. Hopefully, those would help you consider PostgreSQL despite the popularity advantage that MySQL has.

I came from a T-SQL background. I’m fairly fluent, or at least I was on MS SQL server. I could write views, functions and most important, stored procedures quickly and easily.

That was a while ago. Now I work in PostgreSQL land. It took me some time to figure out how to return a record set from a PGSQL function. Now that I’ve figured it out I’m writing more and more complicated functions and my code is much faster.

For me, the lack of documentation about this in the otherwise robust PGSQL docs was the cause for the steep learning curve. Endless googling finally paid off.

Here are the links I learned the most from:
http://use.perl.org/thinc/journal/21802http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.htmlhttp://www.faqs.org/docs/ppbook/book1.htm

A few things I learned and like are the use of a rowtype as a sort of typed array variable. This correlates to a table variable in T-SQL. The cool thing about %ROWTYPE is that if you like it to an existing table, and you change the table later, the type based on that table will include any changes.

The other cool thing I really like is the looping structure listed below:
FOR { record_variable | %rowtype_variable } IN select_statement LOOP
statement;
[...]
END LOOP;

What’s cool (in my opinion) about this is that you have a built in looping control structure. You don’t have to waste a ton of overhead creating and looping through a cursor. If you want cursors, you can have them but you don’t really need them in PGSQL.

I have, by no means, mastered Postgresql but I have learned the basics and I’m digging it more and more. If you’re just starting out using PostgreSQL, be patient. The benefits are well worth the learning curve.