diff -c pgsql mysql (Was Database Name Case Sensitivity)

On Thu, 5 Apr 2001, Brian T. Allen wrote:

> I am sure it is a Good Thing, thank you for the explanation. I will just
> have to change my thinking to all lowercase and get used to it.
>
> Sort of on the same topic, does anyone know of a PostgreSQL primer for those
> making the change from MySQL. Changing religions :} is never easy,
> something to ease the transition and point out the differences and gotchas
> would be very helpful.

Let me not add to any flame wars here, please.

MySQL might be useful for the most complicated database project in the
universe, and might be scalable to counting the grains on sand on every
beach. However--it's support for many 'high-end' database features is
scarce (to it's credit, in its place is an extremely easy-to-install,
easy-to-administer, relatively speedy and painless database that works
great only practically every OS you'd care to use.)

I don't have a simple list of 'gotchas' -- instead, I wanted to answer the
other question. What are the non-gotchas, non-simple differences between
the two.

[Actually I have some gotchas:]

- MySQL uses nonstandard '#' as a comment line. Pg doesn't. Use '--', both
understand it

- MySQL uses ' or " to quote values, as in WHERE name = "John". Pg uses
only single quotes for this, double quotes are used to quote system
identifiers (field name, table names, etc.)

- The whole case-sensitivity issue, above.

- Pg and MySQL seem to differ most in handling of dates, and the names of
functions that handle dates.

But (for me, at least) are more than
just 'how do I do this MySQL thing in PostgreSQL', but 'is there a much
better way to think about this, which MySQL never even supported.'

For example:

Imagine you're building a dynamic web for human resources. You want to
list every current senior staff member's name, some info about them, and a
list of their
goals.

With MySQL, you'd do something like this:

(this is generic pseudo-code, it would easily translate to PHP, Zope,
EmbPerl, etc.)

<in sql="SELECT staffid, firstname, lastname FROM Staff
WHERE datefired ISNULL and seniorstaff = TRUE">
<h1>$firstname $lastname</h1>

<ul>
<in sql="SELECT goalinfo FROM Goals WHERE staffid=$staffid">
<li>$goalinfo
</in>
</ul>

</in>

That's great, and it works fine. You can easily translate this to
PostgreSQL.

Would you want to, though? PostgreSQL has many features MySQL doesn't,
like:

* views
* procedural languages
* triggers
* customizable aggregates
* transactions

For instance, rather than coding in the web front end the logic of
is-not-fired and is-senior-staff, in PostgreSQL, I'd make a VIEW of all
staff for which we want to show goals:

CREATE VIEW staff_having_goals AS
SELECT staffid, firstname || lastname as fullname
FROM Staff
WHERE datefired ISNULL and seniorstaff = TRUE
ORDER BY lastname, firstname

Now, my web programming doesn't have to worry about the lower level
concerns. Imagine if this same list of people and goals appeared dozens of
times on your site--I've moved from having this scattered in many places,
to having it encapsulated in one place.

PostgreSQL also allows procedural languages (perl, tcl, python [alpha],
and an Oracle-alike, plpgsql). These allow you to create functions in your
database (and even non-sysadmins can use them, as the functions fit in the
PostgreSQL security model).

[ Yes, MySQL has user functions, which last time I checked, had to be
written in C, and linked into the database. A nice feature, to be sure,
but VERY different from having high-level procedural languages usable w/o
root privileges! ]

We might use these procedural languages to create lists, handle database
events (if a record is added here, automatically track this here,
etc.) (You might have a function to calculate a staff member's hourly
compensation from their salary, which, IMHO, *should* be a database
function, not a function coded for every different web project or
front-end project you had.)

PostgreSQL also has transactions, which can remove some of the hairy
if-an-error-happened-back-out-all-database-work code. (MySQL, to its
credit, has transactions in their new MaxSQL thingie.)

So:

The things that are handled differently are fairly small.

The real lesson is to learn about what features PostgreSQL has an figure
out *why* to use them!

I'd start w/the five about (views, procedural languages, triggers,
customizable aggregates, transactions) and make sure that you understand
exactly what they are, how to use them, and how wonderful they are.

I hope this helps, and I do hope it doesn't sound condescending. I moved
to PostgreSQL from using MySQL, and for several months after first playing
with it, I just thought it was a bigger, more complicated database that
did 'the same stuff' as MySQL. It took me a while to really realize how
great the 'other' features are.

Good luck!

--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington