mysql issues - Mailing list pgsql-general
| From | George Johnson |
|---|---|
| Subject | mysql issues |
| Date | |
| Msg-id | 001001c0632a$2073a540$0300a8c0@jdsc Whole thread Raw |
| Responses |
mysql issues
|
| List | pgsql-general |
Hi,
My mailer crashed, but before I had to delete all the messages I saw a thread regarding making it easier for mysql users to port over to postgresql. Well guys, you've gone and made a hole for yourself, ESPECIALLY by adding the limitless row lengths in 7.1. With the performance gains, reliability, history and now the ability to not have to deal with the 8k row limit length, you're going to have a scarey number of "how do I do this?" from converted mysql users (myself being new as of last week). I'll hold off telling my 10,000 friends about how great postgresql is until you decide how you're going to deal with your success (kidding). :-0
I've been able to convert my interface applications from mysql sql to postgresql specifics pretty easily. There are some things that required some tricky workarounds, but come to think of it, the majority of sql statements had to be carefully examined and most NOT reworked. But then again, I tried to stick to SQL standard stuff rather than mysql specifics. I am willing to bet many mysql users aren't in the same boat I am -- and possibly their SQL savvy isn't at a "been there done that a few times" level.
Some things that created problems:
"drop table if exists tablename"
[oh boy, is this a great one. imagine not even having to worry if the table is there or not]
"alter table tablename drop column columnname"
"replace into tablename (col1,col2,col3) values (val1, val2, val3)"
[ replace is the combo of a delete and insert, sorta like a cover yur butt update
statement ]
"show tables" -- a great way of just getting a listing of the tables in ur database.
Some things that were problems, but should not be changed:
postgresql requires stricter syntax with GROUP BY, and combining grouping
functions and non-grouping columns. That's just mysql letting crappy SQL slide, which it should not. Mysql has some JOINS and other add-ons that are used because of its lack of subselects. I AM WILLING TO BET everyone who is using the more exotic joins and functions started out beating their heads because mysql had no subselects, which would have made things oh-so-much easier.
You end up creating a crazy amount of temporary tables to get around the lack of subselects, doing crazy optimizations to deal with having so many temporary tables, etc.
Some things that were problems, but should change:
Mysql has a richer set of date and time functions and easy-to-use conversions. PostgreSQL should have that at some point.
I don't know if postgresql has set and enumeration data types ... or if Array covers that. But as far as storing data, nothing beats an automatic conversion of 2 million City names into a set of 150 city names and only the needed bits to store the city name per row, with a cross reference to the enumeration or set value. I'm sure there are better examples of that.
Other than these things, the only big time differences are some different function definitions (which can be easily I'd assume implemented) and just who's gotten farther along in implementing SQL92 or whichever one of those standards everybody strives toward.
The big problem is going to come from users of MySQL who are not experienced SQL DBA's and who relied heavily on the snappy features of MySQL (a la web administration stuff).
I hope this helps a little bit on your decision about whether to expend energy toward making MySQL Friends & Family.
George Johnson
pgsql-general by date: