By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,661 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,661 IT Pros & Developers. It's quick & easy.

Differences between postgres and mysql

P: n/a
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

Nov 22 '05 #1
Share this Question
Share on Google+
11 Replies

P: n/a

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

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

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

Nov 22 '05 #2

P: n/a
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

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

Nov 22 '05 #3

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #4

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #5

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #6

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #7

P: n/a
Mike Nolan <no***@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

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

http://archives.postgresql.org

Nov 22 '05 #8

P: n/a
> 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

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

Nov 22 '05 #9

P: n/a
>>>>> "TL" == Tom Lane <tg*@sss.pgh.pa.us> writes:

TL> Mike Nolan <no***@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: kh***@kciLink.com 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

Nov 22 '05 #10

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #11

P: n/a
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 ji*@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?

http://archives.postgresql.org

Nov 22 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.