Switching from MySQL to PostgreSQL – tips, tricks and gotchas?

Question

Just went through this myself, well I still am…

Case sensitive text
Lack of INSERT IGNORE and REPLACE
Explicit casting needed almost everywhere
No backticks
LOAD DATA INFILE (COPY is close, but not close enough)
Change autoincrement to SERIAL
Although bad form in MySQL, in Postgres, an INNER JOIN without an ON clause can’t happen, use CROSS JOIN or the like
COUNT(*) can be crazy slow
Databases are encoded with character sets, not tables
You can have multiple databases, with multiple schemas (MySQL really has just one database and multiple schema)
Partitioning is different
MySQL interval vs. Postgres interval (for time intervals)
Implicit column renaming, Postgres requires AS
Cannot update multiple tables at the same time in Postgres
Postgres functions are powerful. So there is no CALL proc();; rewrite proc() as a function and SELECT proc();.