Why should I learn PostgreSQL?

This may be a dumb question, but I'm wondering if there are any compelling reasons for me to learn PostgreSQL, especially if I'm very happy with MySQL. Let me give you a little background. Most of what I've done with databases has been web-based applications where we have more people reading from the database instead of writing to the database. So far multiple insert concurrency has not been a problem. (i.e. transactions have not been necessary) However, I'm looking to expand my knowledge a bit considering I'm looking into getting smart with data warehousing and perhaps modifying existing applications for better data protection. Commercially, I've primarily used MS-SQL or Oracle, however some of us have been looking into alternate technologies.

Either way, simply put, I'd like to have some really good reasons to look into something other than MySQL. No offense, but please do two things when answering:

1. Make sure you're current on what each database can do. Either do to when a post was posted or user knowledge, it seemed that some of the posts I've looked at didn't have the most up-to-date information. Please keep in mind future MySQL features as I've been keeping an eye on them, especially sub-queries.
2. If you have a reason, please include why it's a good reason. This was something else that seemed to be missing from some of the posts I've seen. For example, and this is a simple one, transactions are a good feature because they can ensure proper data integrity when issuing multiple insert/update/delete commands or when you want to update a table/insert into a table based on the results of a sub-select statement.

One additional question I'd like answered is whether or not foreign key constraints are truly useful when the application you're writing isolates the user from the actual SQL code? I fully understand the need for such constraints if the user is performing queries directly, but, at least in my experience, we've made it a habit of leaving them off mainly because the haven't been necessary. The code we've written has been structured to ensure things are deleted correctly and has been tested to ensure it works properly. Basically, I'm looking for alternate opinions.

Thanks in advance.

I have been using Oracle for years so that's where most of my experience lies. When I went looking for an open-source alternative PostgreSQL seemed to have just about all the functionality as Oracle. (Oracle is too expensive)

The biggie for me was a stored proc language very similar to pl/sql. I write all my page validation routines in stored procs so this is very necessary to me. AFAIK mySQL does not support this.

I also cannot live without transactions, actually I don't know how people get away without.

I have been using PostgreSQL for about 2 years now and love it. The reason I never liked MySQL is due to the fact that their was no data intergrity. I have been doing database work for a while now and think that any database that doesnt have proper data integrity isnt worth using. Yes you can do what you can to ensure that everything is done right in your code but despite this I have seen databases with rouge data in them even when this was all done. I prefer to have proper data integrity in a database.

Yes, PostgreSQL has much better data integrity than MySQL, but that is only the beginning of the difference between them. To me the most interesting difference is in area of logic:
1. The expressive relational power that PostgreSQL gives you in managing and querying your data.
2. Its power to encapsulate logic in the database (views, constraints, triggers, stored procedures, rules).
3. It's extensability and flexibility, such as the ability to create your own datatypes and operators.

In fact, I would argue that in logical capabilities, PostgreSQL has advantages beyond some of the best commercial systems, with the possible exception of Oracle.

So, while it doesn't have some of the robust enterprise features of the big boys in the field (at least, not yet ), it definitely has the logical capabilities.

Really, the answer to your question depends on just how much you understand about relational database technology itself, and why it is important. I will just give you a few links to help you further explore what I mean:

http://brainscraps.com/faq/pg_my.html (my personal rant on PG vs MY)

Some of my posts in various forums:

http://www.webmaster-forum.net/view...=133&highlight=
http://www.sitepointforums.com/show...?threadid=90658
http://www.sitepointforums.com/show...threadid=100966
http://www.sitepointforums.com/show...?threadid=89045

Some of my old Devshed posts: (warning: I have learned a few more things about relational theory since then)
http://forums.devshed.com/showthrea...&threadid=35171
http://forums.devshed.com/showthrea...&threadid=31435
http://forums.devshed.com/showthrea...40&pagenumber=2 (2nd page of a long, interesting thread)

And, if you really have a burning desire to learn what this "relational" stuff is all about, these sites will point you in the right direction:
Relations and Relationships
www.dbdebunk.com
www.thethirdmanifesto.com
comp.databases.theory

Read your personal rant about MySQL vs Postgres, great work, btw are you going to continually update that doc for each version new release version of Postgres and MySQL, or are you pretty much happy with it and going to let it stand as a complete document with correct info as of its writing?

BTW for anyone who is following the ongoing "industry RDBMS" vs. true relational databases discussions, has anyone tried Alphora or Required Technologies that Fabian Pascal mentions in this article ?

-b

I'll actually be trying to expand that into a list of FAQs on PostgreSQL, perhaps some examples of RULEs, functions, etc...

And, I hope to keep it up-to-date. My interest is in helping people to understand some of the more advanced logical concepts available in PostgreSQL that you don't seem to find much documentation about. I have a feeling many PG users are designing the same databases they would have designed with MySQL, which is a grave mistake.

Yes, I have tried to play with Alphora, but gave up in disgust over all the .NET stuff I had to install and troubleshoot. I suppose it'll be great for those .NET lovers, but I want something simple and open in nature. I noticed that there are a couple of interesting open-source projects listed at http://www.hughdarwen.freeola.com/T...b/Projects.html (Hugh Darwen and C.J. Date are some of the prime thinkers behind the concepts we are talking about here.)

I will be very interested in seeing what comes of these open libraries. For example, I see no reason why someone couldn't take PostgreSQL's great back-end, and develop a "D" relational implementation for it. (wish I had the time, and the C expertise for that...)

Thank you for your responses. The information has proven to be a bit more insightful that what I've read so far. Let me address a few of the things discussed.

1. hedge: good point on the stored procedures. MySQL is planning on having this feature, however I think I'll be needing a bit of knowledge in using it sooner than later.

2. This may be a dumb question, but how does MySQL NOT have data integrity? If there was no such thing, than how do sites like Yahoo and slashdot manage to keep on trucking? Aren't features such as foreign key constraints and transactions used to help preserve data integrity? This seems to be a very inaccurate statement from everything I've seen. Better data integrity may or may not be a true statement, I'm not experienced enough to argue that point, but a complete lack of data integrity I find very unlikely.

3. Rycanor: thanks for the good info. You're posts have been interesting reads. I have just a couple questions: what do you mean by rules and what other types of constraints are there? I know of FK constrainds, PK/Unique key constraints, and non-null constraints, IIRC.

Thanks in advance.

No, anyone who says MySQL has no data integrity doesn't know what data integrity is. Every database system has some level of data integrity, but some allow you to have much more control over exactly what you can make the system do.



The term 'constraint', as applied to database systems, is kind of a general term. It means the whole range of things you can do to direct your database to allow, or not allow certain things. MySQL has very few options for this, such as primary key, foreign key, NOT NULL, etc...

An example of the type of constraint MySQL is missing would be the CHECK constraint, which allows you to add additional constraints to any column in a table definition, such as

CREATE TABLE product(
id int NOT NULL,
name text NOT NULL,
price numeric(11,2) NOT NULL CHECK(VALUE >= 5)
);

This would not allow a product price to be less than $5. This is a small, simple example; the constraint can be at any level of complexity, giving you an amazing amount of power over your data)

Theoretically, constraints can be placed on columns, tables, and even the whole database. The idea here is that you should be able to arbitrarily decide what rules your database should play by to keep your data in a certain type of internal consistency, such as firing a trigger to update the REQUEST_PARTS table if any PRODUCT quantity falls below 100 (an example of a database-level constraint)

RULEs are a special case of constraints available to PostgreSQL users, which essentially does for any query on a table what mod_rewrite does for URL requests in Apache. It allows you to re-direct the action of a query to do something else internally. For example, if you wanted to record a log everytime someone views the data in a certain table, you could just create a RULE on that table, specifying DO ALSO INSERT INTO logtable values(NOW(), CURRENT_USER). So, everytime a user in the database views that data, you have a log of when that happened.