Database independence

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.)

Note: It would also be nice to be able to use SQLite for automated testing purposes. A suite of automated tests typically needs to restore the DB to a pristine starting state several hundreds of time. With DBMS like MySQL, restoring the TIKi db can take something like 30 secs. With SQLite, it's about 5 secs (cause everything is in memory). Note that most of the time, the DB used for testing is very small and only contains a few objects and pages,so keeping it all in memory is not a problem..