Re: Need Some Recent Information on the Differences between Postgres and MySql

Thomas Kellerer, 25.06.2010 14:32:
> Wang, Mary Y, 25.06.2010 01:04:
>> Hi,
>> I'm trying to find some write-ups about the differences between Postgres
>> and MySql. A lot of stuff showed up on Google, but most of them are old.
>> I saw this wiki over here
>> http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and
>> plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered
>> by EnterpriseDB.
>> Are there any other most recent summaries on the differences between
>> Postgres and MySql?
>> Thanks in advance
>> Mary
>
> My favorite features in Postgres that MySQL doesn't have
>
> - deferrable constraints
> - sequences
> - check constraints
> - windowing functions
> - recursive common table expressions
> - the absence of a program to check the consistency of the data
> - the ability to use a subselect in a DML statement that references the
> table to be updated
> - generate_series()
> - array handling
>

And another thing:

The following works in Postgres (and Oracle, DB2, SQL Server, Derby) but not in MySQL (using InnoDB):

create table fktest (
id integer primary key not null,
name varchar(20),
parent_id integer
);
alter table fktest add constraint fktest_parent foreign key (parent_id) references fktest(id);

insert into fktest (id,name,parent_id) values (1,'Root', null);
insert into fktest (id,name,parent_id) values (2,'Sub1', 1);
insert into fktest (id,name,parent_id) values (3,'Subsub', 2);
insert into fktest (id,name,parent_id) values (4,'Sub2', 1);
commit;

delete from fktest where id in (1,2,3,4);
commit;

MySQL complains that it cannot delete the rows"Cannot delete or update a parent row: a foreign key constraint fails"

Regards
Thomas