[einhverfr]

First, I get frustrated whenever I have to use MySQL. However, if you think the MySQL tools that come with the software seem immature you should try Oracle's. There's a reason why TOAD for Oracle is the de facto admin tool and why it is not made by Oracle.

I worked around 2 years with Postgres as a developer and I loved it. Very stable, very solid, many features and no surprises.

Every database has surprises. Oracle has fun with transactions and DDL, null handling of string types, etc). PostgreSQL has surprises galore when dealing with collections and no these are not well documented.

For example the following two are handled very differently by PostgreSQL because nobody can agree on what correct behavior is for collection tables:

     CREATE TABLE foo (
            bar int not null,
            CHECK (bar > 0)
      );
      CREATE TABLE bars (foo foo);

      INSERT INTO bars (foo) values (row(-1)), (row(null));

The above is allowed, but:

      CREATE DOMAIN baz int not null check (value > 0);
      CREATE TABLE foo (bar baz);
      CREATE TABLE bars (foo foo);
      INSERT INTO bars values (row(null)); --not allowed
      INSERT INTO bars values (row(-1)); --not allowed

The thing is that in collections domains are not handled like column types. You'd think this was intentional but if you:

     ALTER TABLE foo ADD is_bar NOT NULL DEFAULT true;

Postgres will happily refuse to do so, saying it can't follow this if foo is used as a type on another table. There is obviously a bug here, but as discussed repeatedly on various email lists, nobody can agree on what needs to be done about it.

Though if you are working with collections I suppose you can assume you are going to find all sorts of surprises. As I say multiple table inheritance is far better as long as you can think in terms of composition instead of OO inheritance.