Re: MySQL refugee interested in pgSQL

> I am a MySQL guy. I am interested in pgSQL. I have learned roughly
> everything I know about databases from using MySQL, and all that that
> implies. I would like to learn about PG and about "real" relational
> database theory. Links to any type of "PGsql for MySQL vets" and/or "Real
> relational database theory for MySQL vets" documents would be appreciated.
>

I got a lot of great information from "An Introduction to Database
Systems" by Date. That book does an excellent job of showing the value
in relational databases over other kinds of databases.

An important part of that is the ability to manipulate information into
the preferred form before sending it to the application. In relational
theory, the storage of the data isn't important because you can always
use a view or a stored procedure to get the data in the form you want.

Another important part is that when you perform an operation on a
relation, you get another relation back. A mathematical analogy would
be: if you multiply two numbers, you get a number. This is known as
"closure" of a set of values to a set of operations. This idea is
important because it allows the seperation of data storage and views, as
described above, as well as more complex manipulations of data. Back to
the analogy, think how long calculations would take if you multiplied
two numbers and didn't get a number back. You could still calculate, but
it would be much more difficult to break the problem down into simple
steps.

Now, whether this is accurate or not is a matter of opinion, but many
people consider PostgreSQL to be more correct regarding the relational
theory and therefore more able to realize the above benefits. I'm not
sure of the current state, but at least in the past, MySQL provided no
way to use the result of a "SELECT" other than to send it to the
application. It had no views, or subselects, or "table functions", or
stored procedures. So, in effect, you were required to select only from
tables that were physically stored on disk. You couldn't, for example,
select from the result of another "SELECT". That removes the benefits of
using a relational database.

Many people also consider PostgreSQL better at maintaining the
consistency inside the database rather than depending on the application
to check the consistency. A good example is that MySQL will accept
"2004-02-31" as a date (I think it thinks it's march 3rd or something
weird, depending on how you treat it), whereas PostgreSQL will report an
error and roll back the transaction. It's certainly possible to do all
consistency checking in the application, but it's easier for me (as a
developer) to rely on PostgreSQL's own strong data integrity protection
and durable transactions. The alternative is to constantly have to take
into account "what if the power went off right at this line of code?".
And if you don't take that into account, you could have a database that
looks correct to the database (i.e. it doesn't have any damaged pages
and all the tuples are accessible), but looks wrong to the application
(e.g. you have an order record but no customer record to associate with
it).

These are some of the primary reasons why I think that PostgreSQL (among
others) delivers more than MySQL.

Hope this is helpful,
Jeff Davis