What is the difference between MySQL and PostgreSQL in terms of writing SQL queries?
(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated):

Lots more features.


CHECK constraints
True SERIALIZABLE isolation
Arrays (including index support for arrays)
Window functions (lead, lag, row_number, etc)
Common table expressions (WITH queries) including recursive CTEs and writeable CTEs
The hstore hash type and its index support
SQL/XML support
json support
A rich set of functions and operators for many types
Partial and function/expression indexes, including partial unique indexes and unique expression indexes
Composite types
User defined aggregates and operators
Set-returning user defined functions
User defined types
Transactional DDL (this is wonderful)
GIN and GiST indexes for complex indexing needs, including K-nearest-neighbour indexing
PostGIS
UNLOGGED tables (though MyISAM meets this need in MySQL)


See the comprehensive user manual for details.

However:


There are no session variables in PostgreSQL, unlike MySQL. (You can abuse GUCs for this, but not to do the same sort of things you do with MySQL session variables).
PostgreSQL has no INSERT ... ON DUPLICATE KEY UPDATE ("upsert")
At the moment, PostgreSQL has different replication options than MySQL. MySQL has no point-in-time recovery, but PostgreSQL has no logical replication (yet, we're working on it).
GROUP BY ... ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.
PostgreSQL doesn't have a built-in event scheduler. You generally just use cron or the Windows Task Scheduler; there's also PgAgent from PgAdmin-III.