I am considering the switch (mainly due to the greater permissive license), and often hear lots of Internet murmuring about how exactly far better Postgres is than MySQL, although not many specifics. Where do you turn in Postgres which make you more productive, or else you find elegant?

It does not need to be fancy, for instance a number of my personal favorite reasons for MySQL include

  • easy primary key incrementing with AUTOINCREMENT (needing to write an electrical generator for each table appears much more of a discomfort than it ought to be for this type of common requirement),
  • "LIMIT,OFFSET" claims (creates easy pagination)
  • ON DUPLICATE KEY UPDATE (makes placing/upgrading "many to a lot ofInch tables fast and painless)

PostgreSQL's most helpful features (which MySQL lacks), for me, are:

  • generate_series and hang coming back functions generally
  • capability to use correlated values in LIMIT and OFFSET clauses
  • Custom aggregates
  • DISTINCT ON clause
  • More complex JOIN techniques (MERGE JOIN and HASH JOIN)

That you can do miracles together.

PostgreSQL code also frequently looks more elegant (observe that "looks" does not mean "works"), since use nice casting syntax (::), nice RECORD types and most of these stuff.

Disadvantages are:

  • You can't use hints (I understand it's intentional I understand they must be prevented go downvote me)
  • You can't use session variables without use of server configuration files (you have to set custom_variable_classes)
  • DISTINCT and GROUP BY procedures are laggy.

Since these two systems are very effective and well-developed, they differ mainly such fancy features (that many designers never make use of).

For fundamental SQL, they are both good.

  • Transactional DDL - that you can do "start transaction; delete table foo; rollback;" and foo it's still there.

PostgreSQL, via PostGIS, offers very wealthy support for geospatial operators. It's difficult to assume doing any type of google maps integration (or similar geospatial work) with every other DB.

  • Saved Methods / UDFs in Perl
  • Asynchronous Database Access in libpq
  • Schemas and databases, not databases pretending to become schemas
  • GIN and GIST

Adding inspections to fields. For instance:

CREATE TABLE "FILES" (
    ...
    md5checksum text NOT NULL,
    CONSTRAINT "FILES_md5checksum_check" CHECK ((md5checksum ~* '^[a-f0-9]{32}$'::text)),
    ...
);

md5checksum area has become always validated it's hexadecimal string and it is 32 figures lengthy.