Search code examples
mysqlpostgresqlmigrationload-data-infile

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

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

Solution

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