Re: Differences between postgres and mysql

On Wednesday 18 February 2004 11:50, Mark Cubitt wrote:
> the reason I ask is I have always used postgres at home and work, but my
> new web host only has mysql :(
>
> I want to know what sort of differences I will have in regards to
> programming php/perl webpages using the database.

Perhaps your best bet is to go over to http://techdocs.postgresql.org and
check out the porting sections.

> I current use sequences in postgres and understand this will be different
> in mysql, so can you tell me what the difference will be and if there are
> any other differences I should look out for.

Hmm... assuming MySQL 3.x (likely in a hosted environment I'd have thought)
- no UNION
- no sub-queries (iirc)
- no views
- no foreign-keys
- sloppy datatyping if you're feeling strict, simplified checking if you're
feeling generous
- actually, silently fails on a number of operations (data validation,
foreign key creation etc).
- no triggers/procedural languages
- only one sequence per table (typed as autonumber)
- no transactions on ISAM tables
- performance can fall off rapidly with multiple writers
- I don't think you can do a hot backup with the transaction-based storage
libraries

In it's defence:
- SELECT count(*) FROM foo; can run much faster
- Other simple queries also perform well

There are hosting companies who will do PG for a reasonable price - is it
worth the pain of switching?

--
Richard Huxton
Archonet Ltd