Ivan wrote:
> I use the MySQL database on the web for the forum and for a news system. I
> would like to know what are the advantages of pgsql over MySQL. I am
> entering in the world of databases so i don't know much as I am 16 years
> old.
When you use PostgreSQL and experiment with it a little,
you will quickly find yourself using possibilities that you
would probably not consider using MySQL, such as referential
integrity, stored procedures, constraints, and transactions.
Those are all essential database functions, even if they aren't
quite as essential as, say, indexes and joins.
I can't talk about MySQL 4.x or MySQL with InnoDB, but when I
used lesser versions I often found myself frustrated since I
*like* referential integrity, I *like* forcing values to fit in
exectly the range I want, and I *like* updating several related
tables while other people are using the database without fear
of them getting inconsistent data. Haven't yet used stored
procedures, but I suspect I'll come to like like that too.
When I use PostgreSQL, I find myself using the database as a
reliable source of consistent data, instead of as a loose-leaf
binder with lots of data that has to be checked every time I use
multiple tables. Of course I don't expect lines to disappear
from tables without warning, but *knowing* that *whatever* the
bugs in my code (except if I drop the foreign key ;-)), if I
select a line containing item=543,person=234,date=xxx, then
the database *guarantees* that "select * from person where
person=234;" will yield a name and a telephone number in a
specific format, even if some other process decided to delete
that person yesterday . . . but couldn't, since he had an item
outstanding . . . well that's just priceless.
I think MySQL does some of this now, not all. I don't really
care anymore :-) But I don't think you'll find yourself using
the features naturally since they haven't been there as long.
Oh, and the query analyzer is cool.
Not quite off-topic: using a language with exceptions, such
as Java, in relation with an RDBMS with transactions, such as
PostgreSQL, makes the expression "error-handling" bring a smile
to my face. The joy of seeing an update of multiple related
tables get clobbered 3/4 way through because of some obscure
consistency check, seeing everything rolled back to pristine
state, seeing he program getting back control in just the right
place, and the satisfaction of knowing that nobody else could
have noticed, that makes my day. OK, I know, it's midnight.