[mzllr]

i did a forum search and couldn't find any info on this, so my apologies if this has been talked about before....

but, i was wondering what the advantages or disadvatages were of using mysql over postgresql, or postgresql over mysql.

i currently use php/mysql, but in doing some reading lately, postgresql is touted as "... the most advanced open-source database available anywhere." is this just propaganda or is postgre really the way to go???

any thoughts????

[Arkham_c]

Originally posted by mzllr:

i currently use php/mysql, but in doing some reading lately, postgresql is touted as "... the most advanced open-source database available anywhere." is this just propaganda or is postgre really the way to go???

They both have their advantages. For me, I like Postgresql because it supports a more full featured set of SQL commands (nested subqueries, etc.).

On MySQL's side, it's more plug-n-play with PHP and some of the other stuff out there. Plus MySQL has a better JDBC driver from what I hear (although I have used the Postgresql on with no problems).

[mzllr]

On MySQL's side, it's more plug-n-play with PHP and some of the other stuff out there.

so is there more coding involved to create a site with a postgresql back-end or are the commands just not as 'intuitive' as they are with mysql???

as far as nested subqueries, what advantages does that give you - or i guess more to the point, could someone give an example of what that would be?

[Arkham_c]

Originally posted by mzllr:
so is there more coding involved to create a site with a postgresql back-end or are the commands just not as 'intuitive' as they are with mysql???

as far as nested subqueries, what advantages does that give you - or i guess more to the point, could someone give an example of what that would be?

No, it's not more involved to create the site. It's more involved to get the setup working initially with Postgres. You need a version of PHP with Postgres compiled in, for one thing (www.entropy.ch has one). Then you have to get them talking. Once you do though, postgres acts like MySQL.

As to nested queries, they let you do stuff that you could not do in a single query without them. For example:

Code:

SELECT user_id, name, password FROM Users
WHERE user_id IN (SELECT user_id from user_mailing WHERE mailing='Y')

This is a trivial example, but it's not easy to do without a subquery. In MySQL, you'd have to do two separate queries (the inner first, then use the results to get the outer).

You could accomplish that wil a join maybe, but it would be ugly, and I'm not sure MySQL supports those either.

[mzllr]

you can do joins with mysql, but like you said, it can get messy...

Code:
SELECT * FROM cd_cart INNER JOIN cd_items ON cd_cart.itemId = cd_items.itemId
where cd_cart.sessionId = '$SESSION_ID' ORDER BY cd_cart.cartId asc

thanks for the info, arkham.

[drew22]

The biggest advantage Postgres has, IMO, isn't sub-queries but triggers to stored procedures.
Finding alternative SQL to sub-queries is usually no big deal, and a lot of times the sub-query approach is messier than the alternative. With triggers and stored procedures, you can move business logic out of your app code and into the database. This way you can be guaranteed that when a certain database operation occurs, say a record from table x is deleted, than the following blah, blah actions on will take place on a bunch of other tables. This is important when you are designing a database that will be accessed by different client applications and you don't want to rely on developers correctly implementing the business logic in their app code. But this is not an issue for some web database that will only ever be accessed by your php scripts. When you start developing for the "enterprise", you may have to consider Postgres.

For web apps I think MySQL is the way to go. Version 4.x is one of the fastest databases available right now and has a lot of momentum behind it.

[MadBrowser]

We used to use mySQL, but not since late 3.x versions. I haven't tried the 4.x versions, which I understand include a lot of the features that I moved away from mySQL to get.

We use PostgreSQL now and really like it so I really don't have any reason to go back to mySQL.

PostgreSQL backs our JBoss server and several J2EE apps using EJBs.

Works great.

[mzllr]

cool. thanks for all the info, guys... good stuff.

[wallinbl]

Originally posted by drew22:

Finding alternative SQL to sub-queries is usually no big deal, and a lot of times the sub-query approach is messier than the alternative.

If that's the case, then you shouldn't have used a subquery. There are many cases where a subquery is the only option. If there are other options, they are usually better than using a subquery.