[joedoe55555]

Postgres is definitely a pain in the butt to properly setup in a network, in particular if you have never done it before. (And just talking about non-cluster use)

However, once you know the steps necessary to setup a postgres server, it's a piece of cake. In fact it's even fun because Postgres is a software that comes in pretty much the same packaging in every version. And I can even top that: with pgAdmin it has a powerful, consistent and stable tool to manage databases. (MySQL seems to be miles away from that)

I think the impression that Postgres is more difficult than MySQL roots in the fact that it's more difficult to install at home. (Why so many installation steps? MySQL is basically just installing the package, changing pw and done?)

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

MySQL on the contrary seems like a toy db, at least when you want to do a lot of relationship stuff with foreign keys etc. It feels really awkward that outer white-spaces have no meaning, there is no boolean type and that the admin tools out there seem to be really immature.

Having to work since nearly two years with MySQL, I find it still painful. If you don't store Petabytes of data and don't use it for a highly frequented website, Postgres is probably your choice.

[jeltz]

> I think the impression that Postgres is more difficult than MySQL roots in the fact that it's more difficult to install at home. (Why so many installation steps? MySQL is basically just installing the package, changing pw and done?)

I think much of the blame here can be put on the people writing installation guides on the Internet. The guides often suggest setups more suited for networked databases than for a local database. When I install PostgreSQL for development machines it is just these two steps.

  1. sudo apt-get install postgresql-9.1
  2. sudo -u postgres createuser -s `id -un`

After that I can create whatever databases I like.

So it is the same number of steps to setup a PostgreSQL and a MySQL database. I admit it took me some time to figure out that this was the simplest way, and I have yet to see a installation guide which suggests this method.

[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.