Peripheral Links
Header And Logo
Site Navigation
| This page is user contributed documentation. See the bottom of the page for information about the author. | |
mysql 2 pg
Things to find out about when moving from MySQL to PostgreSQL
Last updated 8th April 2001MySQL might be useful for the most complicated database project in the universe, and might be scalable to counting the grains of 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 on practically every OS you'd care to use.)
Some Differences Between PostgreSQL + MySQL
In general, PostgreSQL makes a strong effort to conform to existing database standards, where MySQL has a mixed background on this. If you're coming from a background using MySQL or Microsoft Access, some of the changes can seem strange (such as not using double quotes to quote string values).
- MySQL uses nonstandard '#' to begin a comment line; PostgreSQL doesn't. Instead, use '--' (double dash), as this is the ANSI standard, and both databases understand it.
- MySQL uses ' or " to quote values (i.e. WHERE name = "John"). This is not the ANSI standard for databases. PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith').
- MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.
- PostgreSQL is case-sensitive for string comparisons. The
field "Smith" is not the same as the
field "smith". This is
a big change for many users from MySQL and other
small database systems, like Microsoft Access. In
PostgreSQL, you can either:
- Use the correct case in your query. (i.e. WHERE lname='Smith')
- Use a conversion function, like lower() to search. (i.e. WHERE lower(lname)='smith')
- Use a case-insensitive operator, like ILIKE or *~
- Database, table, field and columns names in PostgreSQL are case-independent, unless you created them with double-quotes around their name, in which case they are case-sensitive. In MySQL, table names can be case-sensitive or not, depending on which operating system you are using.
- PostgreSQL and MySQL seem to differ most in handling of dates, and the names of functions that handle dates.
- MySQL uses C-language operators for logic (i.e. 'foo' || 'bar' means 'foo' OR 'bar', 'foo' && 'bar' means 'foo' and 'bar'). This might be marginally helpful for C programmers, but violates database standards and rules in a significant way. PostgreSQL, following the standard, uses || for string concatenation ('foo' || 'bar' = 'foobar').
- There are other differences between the two, such as the names of functions for finding the current user. MySQL has a tool, Crash-Me, which can useful for digging this out. (Ostensibly, Crash-Me is a comparison tool for databases; however, it tends to seriously downplay MySQL's deficiencies, and isn't very objective in what it lists: the entire idea of having procedural languages (a very important feature for many users!) is relegated to a single line on the bottom fifth of the document, while the fact that MySQL allows you to use || for logical-or (definitely non-standard), is listed way before this, as a feature. Be careful about its interpretations.)
The Larger Picture
The important things (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 website 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:
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, PL/pgSQL). 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 there, and so on. 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.)
In addition, PostgreSQL supports many standard parts of SQL that MySQL doesn't, such as subqueries, unions, intersections, etc. While you can often program around these, either with more SQL, or more logic in the front-end, the best (fastest, more portable, most abstracted) solution is to integrate this thinking into your query writing and database design.
So:
The things that are handled differently are fairly small, and can generally be handled without too much pain. Especially since you can easily create PostgreSQL user functions that mimic any from MySQL.
The real lesson is to learn about what
features PostgreSQL has and figure out *why* to
use them!
I'd start w/the five above (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. 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
Director of Information Systems
Support Center of Washington