Well, Simon, I think the difference _is_ functional, not conceptual. But these are some of the functional things that make it powerful, and perhaps they will expand your concept of what it can do. Most important, to me at least, is the ability for the language to be extended by other languages. Currently, I use PgSQL extended by Psql/PL, which is an extension that comes with the language. It allows me to define functions that are stored in the database model, and these functions can perform access to the database quite quickly. A case study would be the first time I realized I _had_ to use a function. I have crumbs (you know, those little strings of text that tell you where you are in the site, like "About > People > Partners > John Doe > Resume") at the top of my site. All of my pages are, of course, stored in the database. So, how could I get this returned to me when I call up the page 'Resume', knowing _only_ the page_id of its parent (in other words, I'm absolutely not going to store this whole string of page ids into the row in my table about 'Resume'). A recursive function, of course, that looks up the parent id until there are no more parent ids. Could be done in PHP, with 5 database queries. Or done in one query, "SELECT myFunction(myPageID);" with this extension. Another functional difference is the way PostgreSQL handles tables and databases. In MySQL, you can inquire the database about a table name, column names in that table, etc. In PG, you actually have this data stored in a different part of the database. I'm not really sure how to explain this; it makes more sense once you get into the syntax. Another difference would be its ability to optimize the database for queries, depending on the type and amount of data you have in the database. How can it do this? Well, PG comes with a much more powerful integrity checking ability. Most importantly, the ability to make use of FOREIGN KEYS. (You can name something a FK in MySQL, but it actually doesn't do jack.) There are lots of places on the web that explain what these are if you don't know, and explain it much better than I could.
So, anyway, more speed, better optimization, more powerful advanced functions that MySQL would be absolutely incapable of performing, and referential integrity are my big reasons to use PgSQL. But the language, though SQL, is more complex, expecially when you're trying to do stuff that is "simple" in MySQL, like get the names of all columns in a table, or count table rows.
--Richard
Top
