Re: pgsql vs mysql

Merlin Moncure wrote:
>
> * mysql performance advantage is greatly overstated, although
> postgresql requires you to use certain conventions (example: prepared
> statements) to get comparable performance
> * both databases (IMO) are very stable. in 6 years of workikng with
> both databases, I've never had either 'just crash' without external
> mitigating circumstances, a testimonial to both projects
> * mysql tends to encourage development in application code, while pg
> tends to encourage development in the database iteself. For various
> reasons, I greatly prefer the latter.
> * pg, in my opinion, has a better unicode handling, although there is
> a small learning curve to do it the best way
> * pg mvcc transactional engine is better than innodb (IMO), and faster
> when used properly
> * pg pl/pgsql is much better than (mysql 5.0) stored procedures. you
> also have a lot of other languages to use if you want
> * pg is generally much more flexible and extensible
> * mysql has decent out of the box replication that is easy to set up
> (one day I hope pg get hot PITR which is analagous feature)
> * pg has IMO much better shell and standardized syntax
> * pg query planer rivals top commercial databse engines
> * mysql has a few features here and there which are nice...just to
> name a few, flush tables with lock, multiple insert, etc
>

This is a very good list of differences. I'm going to elaborate
slightly on this one: "pg is generally much more flexible and extensible."

Mysql in general implements features as an afterthought. This causes it
to be a bit less flexible than postgres. While it does contain what 99%
of people want to use (probably because they only use mysql), it has
huge gaps in it's feature set. Here is a great example of what I'm
talking about:

Mysql does not allow you to use now() as the default value of a column.
From their docs:

"The DEFAULT clause specifies a default value for a column. With one
exception, the default value must be a constant; it cannot be a function
or an expression. This means, for example, that you cannot set the
default for a date column to be the value of a function such as NOW() or
CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as
the default for a TIMESTAMP column. See Section 11.3.1.1, “TIMESTAMP
Properties as of MySQL 4.1”. "

So they work around this major shortcoming by giving people the
CURRENT_TIMESTAMP constant for the timestamp column so at least the
people wanting an automatic timestamps are happy. Basically they add
just enough support to do the most common thing.

Postgres, allows all of the functions in a create table statement. This
is legit in postgres:

create table test_tab (data varchar(20), timestamp timestamp default
now() - interval '32.56 minutes');

HTH,

schu