Steven Citron-Pousty gave a talk on PostGIS and has helpfully posted his slides here. It’s a good introductory overview and provides some good examples of basic spatial SQL in PostGIS.
With ArcGIS Server now supporting PostgreSQL as a back end RDBMS and also supporting the use of PostGIS geometries, a lot of new users may be migrating to the PostgreSQL platform. This presentation is a good overview for anyone picking it up for the first time. Thanks, @TheSteve0!