Not really. Most is pretty well standard SQL (CTE optimisation fence pre PG12 being one exception, and there are a couple more, but really it's mostly standard stuff).
The :: syntax for CAST() is also a psql-ism.
Maybe that is what it is now,
but I still have muscle memory using it in Informix.
Maybe. I come from the Oracle world and the majority of these don't apply to Oracle.