[NTLUG:Discuss] MYSql vs. postgresql

[kbrannen@gte.net]

Terry Hancock wrote:
> On Friday 21 February 2003 09:41 am, Paul Ingendorf wrote:
> 
>>Quoting David <david at hayes-family.org>:
>>
>>> * sub-queries, such as: SELECT * FROM SALES WHERE ITEM_COLOR IS IN
>>>   (SELECT COLOR FROM ....).
...
> Coming soon? Doesn't that work in v3.23?  Maybe I'm missing a subtlety here, 
> but I think I have code like this in my current project somewhere.  This is 
> really just an implicit join, isn't it?

Nope.  Real sub-selects are definitely not there in 3.23; which is one of the 
main reasons I started using Postgres.

> 
> Regarding "constraints", BTW, the MySQL way is to just use an ENUM type.

An ENUM could be considered a type of constraint.  But many times people say 
constraint to mean referential integrity, which Mysql does not have.

And ENUM is not a SQL standard type; in fact, the Mysql data types are very 
non-standard.  That may not be important to you, but it is to me, as I try to 
write as portable code as possible so I'm never tied to a single vendor (open 
or not).

> 
> Besides, the amount of checking that *any* SQL language is going to let you 
> do will automatically be limited -- with Python (or any other complete 
> language), I can check for other kinds of consistency like "Does the user 
> have a home directory on this computer?"  which are implicitly beyond the 
> scope of SQL, as well as internal checks (so the burden of doing internal 
> checks is not so onerous).

I understand, I do that too sometimes.  However, there are times (and 
sometimes requirements by the customer) that as much of that code that can fit 
in the database (either thru constraints or stored procedures) be put there. 
This is where "completeness" becomes very important.  My system has 
mysql-3.23.48 on it (I don't know about 4.0.x), and it does NOT have stored 
procedures or triggers.

I just noticed a section called "Featurewise Comparison of MySQL and 
PostgreSQL" in the Mysql manual.  It's an interesting read.  It's also 
somewhat out of date (with respect to Postgres), but that's probably true with 
any comparison but with the most recent versions of both products.

I also note a roadmap in there.  It says:
*Feature*                                          *MySQL version*
Subselects                                         4.1
Foreign keys                                       4.0 and 4.1
Views                                              4.2
Stored procedures                                  4.1
Extensible type system                             Not planned
Unions                                             4.0
Full join                                          4.0 or 4.1
Triggers                                           4.1
Constrainst                                        4.1
Cursors                                            4.1 or 4.2
Extensible index types like R-trees                R-trees planned for 4.2
Inherited tables                                   Not planned

I believe Postgres already has all of those features, including R-Trees.

As I said in another post, DBs are just a tool, pick the correct one for your 
circumstances.  But on a personal note, I'm much more inclined to pick 
Postgres because of its completeness and ANSI SQL standard compliance.

Kevin