IS there much difference between MySQL and PostgreSQL for a beginner like me, using basic select statements and the like, or are the main differences with using more advanced queries?
+2
A:
PostgreSQL supports more advanced queries, it performs better on complicated queries, but is harder to manage.
MySQL is fast, easy to manage, but you can run into it's limitations on advanced queries, stored procedures and the like.
They are sufficiently similar that I'd recommend starting with MySQL but learning PostgreSQL as well.
qdot
2010-10-30 16:19:58
I recently tried the current installer for PostgrSQL 9.0 on Windows. The installation was totally painless and includes the management GUI pgADMIN.
Peter G.
2010-10-30 16:22:23
qdot
2010-10-30 16:34:01
+13
A:
The reason why I usually suggest PostgreSQL before MySQL is because MySQL is far from the standards (SQL-wise). It does not support the use of window functions (8.4 version), common table expressions (8.4), CHECK constraints, EXCEPT/MINUS operator, even FULL OUTER JOINs... Even though you may have never heard of these words, you'll have to use those concepts at some point.
I strongly suggest you to start with PostgreSQL, then you can learn what "real" SQL is. Then, you can decide if MySQL is sufficient or not.
P.S. I started with MySQL and I regretted it. I now use PostgreSQL and I love it.
Vincent Savard
2010-10-30 16:25:12
Good, specific information. Performance results are against PostgreSQL, sometimes having a lightweight SQL is a good idea, especially if you don't need the extra bloat, but you want something that's still SQL, rather than go with the NoSQL data storage paradigms
qdot
2010-10-30 16:37:21
The myth that MySQL is faster than PostgreSQL has not been true for several years now. Especially since 8.3, PostgreSQL scales better in a high transactional write scenario. And the optimizer is a lot smarter when it comes to complex statements. MySQL with MyISAM might still be faster, then you lose all the advantages of a DBMS like transactions and (foreign key) constraints
a_horse_with_no_name
2010-10-30 16:48:06
`EXCEPT` is ANSI while `MINUS` is not. I seldom see the need for a `FULL OUTER JOIN`, which can be reproduced using the more common OUTER joins and UNION. I'd also point out that you're talking about PostgreSQL 8.4+, being that windowing/analytics are relatively new to PostgreSQL. ** [The biggest reason to me for a beginner to use PostgreSQL would be because of MySQL's hidden column in the GROUP BY](http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html) **
OMG Ponies
2010-10-30 17:42:39
I know you can simulate FULL OUTER JOIN (and EXCEPT), but the syntax is much clearer when you can use those. You are right about PostgreSQL 8.4, I'll edit my post.
Vincent Savard
2010-10-30 18:06:47
Wow! we've managed to provide quite a lot of information :) I think the biggest takeaway is that both SQLs really go after different markets, and both are good to know. Whichever you stat with is your preference, whichever you use in the real world depends on your needs (yes, DBMS is neat, sometimes it's an overkill.. See my comment to another answer - a lot of entreprises would use both depending on requirements)
qdot
2010-10-30 22:08:54
+3
A:
While you're just starting out I think you'll appreciate PostgreSQL's pgadminIII GUI tool more than you would those that I've tried for MySQL. This may just be my preference, however.
When you get past the basics you will definitely want to take advantage of PostgreSQL's support of window functions starting in version 8.4
I'd actually recommend PostgreSQL over MySQL for the window functions alone. Note that there are ways to emulate window functions in MySQL.
Adam Bernier
2010-10-30 16:28:39
I totall agree. And recursive Common Table Expressions is just another reason to prefer Postgres over MySQL. And the better compliance with the standard... And check constraints... And deferrable constraints...
a_horse_with_no_name
2010-10-30 16:44:53