I run a big website and would love to switch; the only thing stopping me is that I'd have to manually fix hundreds of carefully hand-crafted queries to support pg syntax.
If someone wrote a "MySQL emulator" layer for postgres, I'd switch tomorrow. (And I'd work quickly to progressively replace emulated calls with real pg SQL -- it's a lot easier to justify the effort after you make the switch than before!)
It really depends how 'carefully crafted' your queries are. If there are things like index hints, you can just strip them out. If you are using non-standard mysql specific syntax, that will be a bit of a pain, but it won't take more than a day or two to fix a few hundred queries.
Where you are going to experience the most pain is that MySQL lets you write idiotic queries like this:
SELECT id, last_name
FROM some_table
GROUP BY last_name;
> If you are using non-standard mysql specific syntax, that will be a bit of a pain, but it won't take more than a day or two to fix a few hundred queries.
One issue is with non-standard MySQL functions (such as inet_aton/inet_ntoa) and aggregate functions (such as group_concat). While most have great and superior pg implementations, it's a lot of work to cross-reference each one, grep the source code, and hope the pg implementation is sufficiently identical.
Another issue is that MySQL considers the 'as' keyword to be optional. I'm sure pg has good reason for requiring it, but there's probably about a thousand 'as'es to be added.
And that's about where I gave up last time, if I recall.
> I'm actually curious: what id would a MySQL user expect to be displayed
> for this query?
MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group:
> Do not use this feature if the columns you omit from the GROUP BY part
> are not constant in the group. The server is free to return any value
> from the group, so the results are indeterminate unless all values are
> the same.
Their example in documentation:
SELECT order.custid, customer.name, MAX(payments)
FROM order,customer
WHERE order.custid = customer.custid
GROUP BY order.custid;
"MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group"
That's what errors are for, not documentation. It is pretty easy to forget something in the group by, and documentation won't help with that.
The dangerous thing is that the result returned from such a nonsense query looks valid in many cases, while being wrong in subtle ways.
PostgreSQL detects when the query is valid, and executes it if so. So, if you do a GROUP BY customer_id (a key column), you can also see customer_name without adding it to the GROUP BY list. But if you group by customer_zipcode (not a key), and try to select the customer_name, it will throw an error.
I run a big website and would love to switch; the only thing stopping me is that I'd have to manually fix hundreds of carefully hand-crafted queries to support pg syntax.
If someone wrote a "MySQL emulator" layer for postgres, I'd switch tomorrow. (And I'd work quickly to progressively replace emulated calls with real pg SQL -- it's a lot easier to justify the effort after you make the switch than before!)