Well, this was a great read! I haven't used Postgres but it looks really powerful. But honestly, aside from the JSON data type and maybe LITERAL JOINS, I could live without that other stuff. Why I still use MySQL and see a use case for it, is because a lot of that stuff you listed as Postgres features I don't like doing at the Database level, I prefer handling it at the Code level. It's a more future proof and flexible approach in my opinion, it's easier to alter a few lines of code than alter Databases, triggers, checks, whatever etc..
+1 for this comment, I have worked on a monolithic project which made use of every single feature of Postgres mentioned above ( I guess the original developer really wanted to try them all).
We struggled everyday trying to debug things and figure out how they work in the database. We spent most of the time trying to remove logic from the database to code, to make the logic more understandable and easier to read.
So, Yes! Postgres is awesome, But please don't abuse these features and ask yourself first. Do I really need to this at the database level ?
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.