For those who have worked with both, do you find the PostgreSQL-only functions and features to be extremely useful or does it not really matter much?
Could I avoid these features? Probably. At the cost of query readability and sometimes possibly performance and/or server roundtrips.
Furthermore, for years now, PostgreSQL is adding features in places that are very interesting to my use case, so I'm usually jumping ahead to newer versions very quickly.
Case in point is the new jsonb support which I'll be using to displace an old, bulky and join-heavy (performance!) system for handling custom attributes of products and of course, once you've gotten a taste of the awesome, you will never want to go back.
From an operations standpoint, I wouldn't say Postgres is any harder to deal with than MySQL either, especially for smaller setups. Bigger ones are probably similar too, though I personally only have experience with bigger Postgres setups.
There's nothing stopping you from trying it out: At this point, both Postgres and MySQL have proven their reliability even in big setups, so when you start your next thing, you won't ever have to be afraid that your pick of database will cause you trouble later down the road.
As such, I would really stop asking questions and start using Postgres.
IME, it's easy to express the direct utility of features like this - you get row numbers, okay, so what? - and hard to express the huge indirect utility - using them as building blocks in complex (but efficient) queries.
They don't understand how bad MySQL is, and how much is weakens our product.
Anyhow, if you really need it, you can easily write upserts with triggers.