I know I know, this topic is probably a cliche by now, but I am looking for real world developers'
and DBAs' experiences from people who have managed/ developed for both.

I've developed for MySQL before but never used PostgreSQL but am interested in doing so. I'll
probably be giving it a shot myself anyways, but with the newer versions of MySQL offering
features like views, SPs etc., what makes PostgreSQL worth the switch? I've gone through many
articles on this topic, but most of them compare features and not experiences which is what I am
looking for.

Please do spare some time, if possible, to share this knowledge.

Don't think there's scope for another mistake in this mail like my previous one
Kunal

PS - Please feel free to forward it to one of the other lists and/ or point me to that list, if
that question will be more suitably answered there.



__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

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

Kunal wrote:
The following is just my personal opinion, based on what
I experienced with these systems. Your mileage may vary,
and I'm certain there are people with conflicting opinions,
so please put flames to /dev/null.

As far as the features are concerned, mysql is still light-
years behind postgres. Yes, they did introduce transactions,
but only with certain table types, and then you can't have
clustering, or something else is limited in absurd ways.
There are many cases of "if you use feature x, you cannot
use feature y". I don't know how much of that was fixed,
because I tried to avoid mysql in the recent past. It may
well be the case that those things have been fixed.

One thing which I like about postgres is its robustness.
I experienced broken mysql tables several times; it seems
to be a very fragile system. It never happened to me with
postgres. You can even pull the power plug from a running
postgres server -- Upon reboot, it will complain loudly at
you, but it will come up and run just fine.

Also, I think it is much easier to debug SQL statements in
postgres. The output from explain analyze etc. is very
helpful in finding out why a query might perform less than
optimal.

Best regards
Oliver

PS: In case it matters, I'm using postgres on FreeBSD 4.x
on UFS and NFS filesytems.

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"I learned Java 3 years before Python. It was my language of
choice. It took me two weekends with Python before I was more
productive with it than with Java." -- Anthony Roberts

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

Kunal wrote:

You can start to read mysql gotchas and postgresql gotchas:

http://sql-info.de/mysql/gotchas.html
http://sql-info.de/postgresql/postgres-gotchas.html


apparently sql-info.de is unreachable now but I hope will be in a while.


Regards
Gaetano Mendola

On Tue, 2004-08-24 at 04:51, Kunal wrote:
Ever heard the phrase "putting lipstick on a pig?"

Don't get me wrong, I don't hate MySQL. I think it's got its uses, but
the problems it has with data validation alone are enough for me to
steer clear of it. For instance, inserting 0000-00-00 as a date, or
just silently munging numbers it isn't sure what to do with.

For instance:
mysql> create table a (bignum numeric(200));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into a values
(1234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+------------------------------------------------------------------------------------------------------+
+------------------------------------------------------------------------------------------------------+
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Notice those numbers don't match, but there's no error telling me my
number is getting hosed up. (This, like so many other things, works
perfectly in PostgreSQL.)

Plus, most features aren't things on a check list that are simply done.
It takes a long time for such features to mature in terms of stability
and performance, and PostgreSQL has a big lead on MySQL in both.

PostgreSQL is quite different in terms of philosophy than MySQL. MySQL
focuses on speed and ease of use, PostgreSQL focuses on stability,
reliability, and correctness first. They are sort of converging, but
it's easier to performance tune a database that does the right thing
than it is to try and add correctness to an established database while
not breaking dozens and dozens of apps already written for it.

google for "mysql gotchas" and see what comes up.



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

At 06:51 AM 8/24/04, Kunal wrote:

What do you mean "newer versions of MySQL offering features like views,
SPs"?? Don't you mean "will be offering"? These features are still a long
way from production code in MySQL. Both features are part of 5.0 release.

MySQL 4.1 (first development release) was announced in January 2003 and is
still in beta.
http://www.mysql.com/news-and-events...e_2003_05.html

The announcement also contains:
MySQL 5.0 (alpha developer release) was announced one year later and is
still listed as Alpha.
http://www.mysql.com/news-and-events...e_2004_02.html

PostreSQL is worth the switch because these and many other features are
available TODAY. Personally, I compared the two products early in 2000 and
chose PostgreSQL at that time.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Hi Kunal,

Even if the features were identical, which they aren't, then you'd
still choose PostgreSQL with your eyes shut since it's totally free.
MySQL is $500 per seat for commercial deployment. (There's a $250
option but it doesn't include InnoDB and then you wouldn't even be
ACID compliant. Google for InnoDB and ACID if you need more info.)

Marc

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

On Tuesday 24 August 2004 3:51 am, Kunal wrote:

Relevant article: http://www.tek-tips.com/faqs.cfm?fid=1751

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

Hi Group:

I used COPY to import a bunch of data into my PG database. The db has
one table so far, and the table has a column using DATE with a
datestyle of US,ISO.

To get all the data to copy correctly, I created a meaningless date
entry of 01/01/2001 and inserted this into every row in the database.

I would like to delete these bogus date entries and leave the date
column blank until proper dates can be filled in.

What command can I use to remove all the 01/01/2001 entries in every row
and leave it blank.

Thank you for your time and patience.

Mike



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

michael (AT) floog (DOT) net writes:
Set the values to NULL, eg
UPDATE mytab SET datecol = NULL;

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Tom,

Thanks for the speedy reply.
I'm going to ssh to my home-box and give this a try right now.

Mike


Quoting Tom Lane <tgl (AT) sss (DOT) pgh.pa.us>:






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