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

Question

I am contemplating a switch from MySQL to PostgreSQL.

What are your tips, tricks and gotchas for working with PostgreSQL?

What should a MySQLer look out for?

See also: How different is PostgreSQL to MySQL?
See also: Migrate from MySQL to PostgreSQL

Note - I don't think this is a duplicate. In particular the type of answers are quite diffferent and the responses here have much more implementation detail, which is what I was looking for

Answer 1

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();.