Web devs who have made the switch from MySQL, what postgresql features could you now not do without?

Question

I'm contemplating the switch (mainly because of the more permissive license), and tend to hear a lot of Internet murmuring about how much better Postgres is than MySQL, but not many specifics. What do you do in Postgres that make you more productive, or you find elegant?

It doesn't have to be fancy, for example some of my favorite things about MySQL include

• easy primary key incrementing with AUTOINCREMENT (having to write a generator for every table seems more of a pain than it should be for such a common requirement),
• "LIMIT,OFFSET" statements (makes for easy pagination)
• ON DUPLICATE KEY UPDATE (makes inserting/updating "many to many" tables quick and painless)

Answer 1

PostgreSQL's most useful features (which MySQL lacks), in my opinion, are:

• generate_series and set returning functions in general
• ability to use correlated values in LIMIT and OFFSET clauses
• Custom aggregates
• DISTINCT ON clause
• More advanced JOIN methods (MERGE JOIN and HASH JOIN)

You can do wonders with them.

PostgreSQL code also often looks more elegant (note that "looks" doesn't mean "performs"), since you can use nice casting syntax (::), nice RECORD types and these kinds of stuff.

Drawbacks are:

• You cannot use hints (I know it's intentional; I know they should be avoided; go downvote me)
• You cannot use session variables without access to server configuration files (you need to set custom_variable_classes)
• DISTINCT and GROUP BY operations are laggy.

Since both these systems are quite powerful and well-developed, they differ mainly in such fancy features (that most developers never even use).

For basic SQL, they're both good.

Answer 2

• Transactional DDL - you can do "start transaction; delete table foo; rollback;" and foo will still be there.

Answer 3

PostgreSQL, via PostGIS, offers very rich support for geospatial operators. It's hard to imagine doing any kind of google maps integration (or similar geospatial work) with any other DB.

Answer 4

• Stored Procedures / UDFs in Perl
• Asynchronous Database Access in libpq
• Schemas and databases, not databases pretending to be schemas
• GIN and GIST

Answer 5

Adding checks to fields. For example:

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

md5checksum field is now always validated that it's hexadecimal string and it's 32 characters long.