473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

Nov 22 '05 #1
11 9912

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
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
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
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
> 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
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
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
> 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
>>>>> "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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: OneSolution | last post by:
I need to decide on which database system to use for our company. What is the popular opinion on PostGres vs. MySql? Thanks, Santosh
6
by: Th3L0rD | last post by:
Hi, I'm porting some php code from mysql to postgres but I canàt find something equivalent to mysql_select_db(). I use this function to switch from a DB on machine 1 to a db on machine 2. Is there...
8
by: wlcna | last post by:
mysql v4.0.16: I had been using mysql with innodb and thought that was fine, until i used it for something requiring a few - perhaps slightly involved - joins, and have now seen the performance...
13
by: Nigel J. Andrews | last post by:
This will be a little vague, it was last night and I can't now do the test in that db (see below) so can't give the exact wording. I seem to remember a report a little while ago about tsearch v2...
5
by: Fabio Benavides Murillo | last post by:
> Hello > > I have a problem, I want to migrate the data of a postgres' database to a > mysql's database, this because i need to move to a windows. > > I tried making a postgres' backup using...
3
by: Gaetano Mendola | last post by:
I found this article: http://www.serverwatch.com/news/article.php/10824_1126981_Ext that is clear out dated, it's anyway a good comparison with mysql. Do you know if someone did the TPC-C...
3
by: warwick.poole | last post by:
I am interested in finding out about Enterprise scale Postgres installations and clustering, especially on Linux. Essentially I would like to know the possibility that Postgres can store the...
6
by: Prabu Subroto | last post by:
Dear my friends... Usually I use MySQL. Now I have to migrate my database from MySQL to Postgres. I have created a database successfully with "creatdb" and a user account successfully. But...
3
by: fjm67 | last post by:
I am new to PHP but not so new to Postgres. If someone can either direct me to some howto or even provide me with an example, I would be grateful. I would like to know if it is possible to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.