I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't even realize the amount of in-line t-sql I would use to just get stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks cannot return resultsets, so short of creating a function and dropping it, it's not possible to get the same workflow.
Just ruminating here, and this has probably already been discussed in the past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...] RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't had any serious problem with creating/dropping functions like you mentioned.
-Michel
The lack of GUI tooling was also a huge "whoa" moment for me, which I still grapple with.