Differences between postgres and mysql

**Paul Thomas** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

On 18/02/2004 11:50 Mark Cubitt wrote:[color=blue]
> 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.[/color]

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 |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Richard Huxton** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

On Wednesday 18 February 2004 11:50, Mark Cubitt wrote:[color=blue]
> 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.[/color]

Perhaps your best bet is to go over to http://techdocs.postgresql.org and
check out the porting sections.
[color=blue]
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Robert Treat** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

On Wed, 2004-02-18 at 06:50, Mark Cubitt wrote:[color=blue]
> 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.
>[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Mark Cubitt** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

cheers everybody for the help, I think I can do what I need with mysql, but
would much prefer postgres.
[color=blue]
> There's a little voice telling me this is a troll, but in case not[/color]

a just another quick question, "troll" ???

Thanks in advance

Regards

Mark Cubitt

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Mike Nolan** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

> the reason I ask is I have always used postgres at home and work, but my new[color=blue]
> web host only has mysql :([/color]

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.
[color=blue]
> I want to know what sort of differences I will have in regards to
> programming php/perl webpages using the database.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Robert Treat** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

On Wed, 2004-02-18 at 09:20, Mark Cubitt wrote:[color=blue]
> cheers everybody for the help, I think I can do what I need with mysql, but
> would much prefer postgres.
>[color=green]
> > There's a little voice telling me this is a troll, but in case not[/color]
>
> a just another quick question, "troll" ???
>[/color]

http://info.astrian.net/jargon/terms/t/troll.html

:-)

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**Tom Lane** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

Mike Nolan <nolan@gw.tssi. com> writes:[color=blue]
> MySQL's poor implementation of the 'alter table' process.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Mike Nolan** · Nov 22 '05, 09:02 AM

Re: Differences between postgres and mysql

> Interesting you should say that, because for years we were getting beat[color=blue]
> 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?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**Vivek Khera** · Nov 22 '05, 09:03 AM

Re: Differences between postgres and mysql

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa .us> writes:

TL> Mike Nolan <nolan@gw.tssi. com> writes:[color=blue][color=green]
>> MySQL's poor implementation of the 'alter table' process.[/color][/color]

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.c om Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**Jeff Davis** · Nov 22 '05, 09:03 AM

Re: Differences between postgres and mysql

On Wed, 2004-02-18 at 03:50, Mark Cubitt wrote:[color=blue]
> the reason I ask is I have always used postgres at home and work, but my new
> web host only has mysql :(
>[/color]

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.
[color=blue]
> I want to know what sort of differences I will have in regards to
> programming php/perl webpages using the database.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Jim C. Nasby** · Nov 22 '05, 09:03 AM

Re: Differences between postgres and mysql

Take a look at http://sql-info.de/mysql/gotchas.html.

On Wed, Feb 18, 2004 at 11:50:23AM -0000, Mark Cubitt wrote:[color=blue]
> 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
>[/color]

--
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?"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

Differences between postgres and mysql

Differences between postgres and mysql

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment