I’ve spent the last year becoming really comfortable with MySQL, but due to its increasing trendiness and my desire to homogenize my web-apps with Heroku, I’d like to start using PostgreSQL for my web apps instead. There are a resources out there for learning PostgreSQL, but I don’t really want to have database concepts explained to me from scratch again, and I don’t want to have to re-learn all the stuff that’s pretty much the same.
What are the critical differences that I need to understand – syntactical and conceptual – between MySQL and PostgreSQL that will affect me on a day-to-day basis?
1
It depends somewhat on how you’re using the database.
If you’re using an ORM, you might not notice any issues at all.
I switched an application to using Postgresql (for deployment to Heroku), but only after discovering situations where the SQL created by Rails worked fine on SQLite, but not on Postgresql. Invariably, the issues were caused when joins were querying the same column name on multiple tables. SQLite didn’t care, but Postgresql wanted the relation name specified if it was in the ‘where’ clause.
Even though I’ve worked with both MySQL and Postgresql, I’m not sure of any fundamental conceptual differences between them. They’re both fairly solid client-server databases, although PG seems to be generating a better reputation.
However, there are definitely some critical syntactical differences between MySQL and Postgresql. I found a decent guide to those here: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
1
What are the critical differences that I need to understand – syntactical and conceptual – between MySQL and PostgreSQL that will affect me on a day-to-day basis?
The biggest one is what the database is there for. MySQL is built, essentially, to be an information store for your application. PostgreSQL is there to be an information management solution for several applications hitting the same data. This difference comes up in many different ways. For example, MySQL lets applications set their own sql_mode which affects what data will be seen as valid. PostgreSQL has no such option. Consequently in MySQL, one is heavily encouraged to think of SQL as being an application-private API, while in PostgreSQL is is an API that can be safely shared between applications. This leads to a lot of little differences. MySQL is probably easier to port an application to, but PostgreSQL is probably easier to port a data environment to….
You may find the following to be of help: http://ledgersmbdev.blogspot.com/2012/09/or-modelling-interlude-postgresql-vs.html