MySQL and PostgreSQL

Kefka

How different are these two when it comes to code?

For example, if I had a site and database done up with php and mysql, and decided i wanted to convert to postrgresql, how much recoding would i be up for?

I imagine the php changes would be fairly minimal... different function names etc...

How about the database? will that be fairly much the same to rebuild?

Thanks,
Kefka

csn

Well, converting the data is pretty straight-forward, but you'll likely find a lot of your code migrating from PHP files into the PG database: subselects, stored procedures, triggers, foreign key handlers, constraints, views, etc. Sooo much nicer.

Kefka

Yeah, I've heard of the nicer and improved features pgSQL has over mySQL....
However at the moment I'm developing on a Windows machine, so as far as I know, pgSQL is pretty much out of the question.

csn

Cygwin lets you use PG on windows (I use it myself).

Sxooter

MySQL has a lot of non-standard behaviour. Using ` to quote a field name instead of ", allowing " to quote a value when the SLQ spec only allows ', and the list tends to get long after that. Basically, it's easier to port FROM postgresql to mysql because Postgresql will likely make you "do it right" while MySQL will let you do it wrong, and then it won't port. Another example is MySQL's allowing you to select a target column in a grouped query without aggregating or grouping said column:

select field1, field2 from table1 group by field1

is "legal" in MySQL, while both the SQL spec and Postgresql claim it is an illegal query. After all, which field2 entry do you want / get with that query? No one knows.

MySQL has an enumerated type that is not mirrored in most other databases, including postgresql.

Lastly, Postgresql has a windows port well under way, so you can start now with cygwin (performance is pretty mediocre under cygwin, but it works pretty well) and migrate to the native version some time this summer. For info on the Postgresql - windows port, go here:

http://momjian.postgresql.org/main/writings/pgsql/project

csn

Yeah, I dumped to CSV rather than SQL when transferring data from mysql to postgres.

csn

Funny stuff from pgsql-general:

But of course, if you're looking for other MySQL "conveniences" such as
silently chopping your string, silently converting your column data
type, allowing entering invalid values in your ENUM column, allowing
invalid dates, allowing breaking FK integrity, etc. then PG does not
have those. But I find them scary anyway :-)

Sxooter

csn: I saw that post go by and was blowing vanilla coke out my nose... pretty funny, in a sad way...

matjaz

ahahahahahaha

you guys seem to like MySQL a lot 😃

Sxooter

Actually, the funny thing is I do like MySQL. I just don't use it for things like accounting or finance, where the math is important. for content management, its built-in full text indexing and light-weight design make a particularly good match. Plus it can collate individual columns by different locales, while Postgresql, great database that it is, collates the whole damned cluster according to one locale chosen at initdb time. i.e. not even individual databases can support more than one locale, and to change the one locale on a cluster, you have to dump the whole thing, re-initdb, then reload it all. Serious pain.

So, they've both got warts. They've both got strengths. I wouldn't use postgresql for a CMS system that needed more than one language / locale in it, just like I wouldn't use MySQL for something where data integrity was my main concern.

I'm not religious about it all, I just try to use the right tool for the right job.