Thread: Differences between postgres and mysql
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. 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. Thanks in advance Regards Mark Cubitt
On 18/02/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. > > 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. I think MySQL has auto-increment columns instead. As for the rest of the differences, I couldn't supply a full list but from what I know: - transactions are only supported on InnoDB tables - foreign keys only supported on InnoDB tables - no sub-queries - no stored procedures - no triggers or constraints - crap/non-existent range checking - no views - don't expect adherence to SQL standards No doubt others will be able to correct/expand this list. I haven't used MySQL for about 4 years and always _detested_ it. Happily, on migrating to Linux, I discovered PostgreSQL and I've not touched MySQl since :) HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
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
On Wed, 2004-02-18 at 06: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. > > 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. > There's a little voice telling me this is a troll, but in case not, take a look at http://techdocs.postgresql.org/#convertfrom which has several article on converting from mysql to postgresql... not exactly what you're doing but might have some valuable info. You might also want to look at http://openacs.org/philosophy/why-not-mysql.html, which is an outdated comparison that breaks out several areas where mysql is not as robust as postgresql. Otherwise I guess I would recommend you ask the mysql community how they work around any missing postgresql features you might need for your apps. Well, actually I would recommend finding a new hosting company (http://techdocs.postgresql.org/hosting.php) but barring that I think your going to have to turn to the mysql community for information about their database. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
cheers everybody for the help, I think I can do what I need with mysql, but would much prefer postgres. > There's a little voice telling me this is a troll, but in case not a just another quick question, "troll" ??? Thanks in advance Regards Mark Cubitt
> the reason I ask is I have always used postgres at home and work, but my new > web host only has mysql :( Have you tried asking them to support postgreSQL? A client of mine had to switch ISP's rather abruptly last fall, in the middle of a lengthy project to convert them to postgreSQL from a legacy environment which includes some work in MySQL both internally and at the previous ISP. They agreed to add postgreSQL support when we need it, which will likely be in the next month or two. They were also very cooperative in providing other tools, like recompiling php to add features we needed, such as support for dBase files. > I want to know what sort of differences I will have in regards to > programming php/perl webpages using the database. The project I'm working on is using pg instead of MySQL because of the feature richness of pg, especially things like triggers. Here's a short list of things I've had to put up with in the MySQL part of that project: Difficulties in updating table A from table B because of the lack of subqueries or the 'update from' syntax in pg. Minor annoyances like needing to write 'substring' instead of 'substr'. Periodic corruption of indexes. Inconsistent handling of case sensitivity in SQL between windows and unix/linux implementations, which makes moving code from one platform to another more vexing. (On the other hand, MySQL appears to handle case insensitivity in data better than pg.) MySQL's poor implementation of the 'alter table' process. -- Mike Nolan
On Wed, 2004-02-18 at 09:20, Mark Cubitt wrote: > cheers everybody for the help, I think I can do what I need with mysql, but > would much prefer postgres. > > > There's a little voice telling me this is a troll, but in case not > > a just another quick question, "troll" ??? > http://info.astrian.net/jargon/terms/t/troll.html :-) Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Mike Nolan <nolan@gw.tssi.com> writes:
> MySQL's poor implementation of the 'alter table' process.
Interesting you should say that, because for years we were getting beat
up regularly about how poor our ALTER capabilities were compared to
MySQL's. Have we really passed them up in ALTER flexibility? Or is
there some other limitation you are thinking of?
regards, tom lane
> Interesting you should say that, because for years we were getting beat > up regularly about how poor our ALTER capabilities were compared to > MySQL's. Have we really passed them up in ALTER flexibility? Or is > there some other limitation you are thinking of? I wasn't commenting on the flexibility issue, more on performance. If you add or delete a column doesn't MySQL copy the table to a temp table then delete the original one and rename the copy? Try doing that on a table with 25 million rows and you can go to dinner and a movie while you wait for it to finish. Try it on a really big table and you can go to a performance of the Ring Cycle while you're waiting. (And I mean the Wagner Ring, not Tolkien.) I didn't comment on the interactive user interfaces. While there are a few things about psql that drive me nuts (like the fact that it always does the edit to a temporary file so it goes away immediately upon exit and some of the ways \o works, I'm used to using both features in Oracle to provide a historical trail of my work), mysql can't even repeat a command (\g) without first re-editing it. -- Mike Nolan
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> Mike Nolan <nolan@gw.tssi.com> writes: >> MySQL's poor implementation of the 'alter table' process. TL> Interesting you should say that, because for years we were getting beat TL> up regularly about how poor our ALTER capabilities were compared to TL> MySQL's. Have we really passed them up in ALTER flexibility? Or is TL> there some other limitation you are thinking of? I think we get beat up on the things you can (rather cannot) ALTER. I think mysql is getting beat up on how they implement the ALTER. From my readings earlier on this list, it seems that the entire backend file(s) are copied upon alter, and all indexes rebuilt. That would be a major PITA for a large database (not that you'd have one all that large in mysql anyhow ;-)) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On Wed, 2004-02-18 at 03: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 :(
>
If you have ssh or other shell access, you can install postgresql as a
user (you may want to check with your ISP first). It will require a
little extra disk space though.
> I want to know what sort of differences I will have in regards to
> programming php/perl webpages using the database.
There are too many issues here to make a brief reply. I'd just look
around for migration tips pg->mysql (although I get a feeling that case
is more rare than mysql->postgres...). In general, don't assume too much
when working with MySQL. MySQL tends to throw fewer errors, so testing
the results will be more important.
Regards,
Jeff
Take a look at http://sql-info.de/mysql/gotchas.html. On Wed, Feb 18, 2004 at 11:50:23AM -0000, 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. > > 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. > > Thanks in advance > > Regards > > Mark Cubitt > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"