Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
1  
There is no capital 'G' in 'PostgreSQL'. –  tdammers Aug 21 '12 at 7:05

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
1  
"SQLite didn't care". Sounds like a good reason to use Postgres. Two tables with the same column name could store totally different and unrelated data. –  mike30 Feb 18 '13 at 18:41

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.