Things to find out about when moving from MySQL to PostgreSQL

Last updated 8th April 2001

MySQL 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, its support for many 'high-end' database features is scarce (to its 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:

• 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, 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