PostgreSQL migration from MySQL

Migrating from MySQL to PostgreSQL is very doable, but you usually need to handle a few differences in SQL syntax, data types, indexes, functions, and application queries.

High-level migration steps

1. Inventory the current schema

   • Tables, columns, indexes, foreign keys
   • Views, triggers, stored procedures
   • Application-specific SQL queries

2. Convert the schema

   • Map MySQL data types to PostgreSQL types
   • Recreate constraints, defaults, sequences/identity columns
   • Review indexes and collations

3. Migrate the data

   • Use ETL tools or dump/export/import
   • Validate row counts and checksums

4. Adapt the application

   • Fix SQL syntax differences
   • Adjust ORM configuration if used
   • Replace MySQL-specific features

5. Test thoroughly

   • Functional tests
   • Performance tests
   • Transaction/locking behavior
   • Edge cases around NULLs, dates, string comparisons

---

Common MySQL → PostgreSQL differences

1) Auto-increment

MySQL:

id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL:

id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Older style:

id SERIAL PRIMARY KEY

---

2) Boolean values

MySQL often uses: TINYINT(1)

PostgreSQL has native:

BOOLEAN

---

3) Text and string types

• VARCHAR(n) and TEXT exist in both
• PostgreSQL does not need TINYTEXT, MEDIUMTEXT, LONGTEXT
• Use TEXT unless you truly need length enforcement

---

4) Unsigned integers

MySQL supports UNSIGNED; PostgreSQL does not.

You may need:

• larger signed types
• CHECK constraints
• application-level validation

---

5) Zero dates and invalid dates

MySQL can allow values like 0000-00-00 in some modes; PostgreSQL does not.

You’ll need to clean invalid date values before import.

---

6) JSON

• MySQL has JSON
• PostgreSQL has json and jsonb

Usually jsonb is preferred for indexing and querying.

---

7) LIMIT syntax

Mostly similar:

SELECT * FROM users LIMIT 10 OFFSET 20;

Works in both.

---

8) Upserts

MySQL:

INSERT INTO t (id, name)
VALUES (1, 'a')
ON DUPLICATE KEY UPDATE name = VALUES(name);

PostgreSQL:

INSERT INTO t (id, name)
VALUES (1, 'a')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;

---

9) String concatenation

MySQL: CONCAT(a, b)
PostgreSQL: a || b or CONCAT(a, b)

---

10) Identifier quoting

• MySQL: backticks `table`
• PostgreSQL: double quotes "table"

PostgreSQL folds unquoted names to lowercase, so be careful with mixed-case identifiers.

---

Schema conversion example

MySQL

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PostgreSQL

CREATE TABLE users (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Data migration options

Option 1: Use a migration tool

Tools commonly used:

• pgloader — very popular for MySQL → PostgreSQL
• AWS DMS if moving in cloud environments
• Custom scripts / ETL pipelines

pgloader is often the easiest:

pgloader mysql://user:pass@localhost/source_db postgresql://user:pass@localhost/target_db

It can handle:

• table creation
• data loading
• type conversion
• basic transformations

---

Option 2: Dump and transform manually

• Export MySQL data to CSV
• Load into PostgreSQL using COPY
• Rebuild constraints and indexes afterward

This is more work but gives more control.

---

Things to watch carefully

• Case sensitivity
• NULL semantics
• Date/time zones
• Charset/encoding (utf8mb4 → UTF8)
• Full-text search differences
• Stored procedures/functions
• Triggers
• Foreign key behavior
• ENUM/SET types
• Query performance and indexing

---

Recommended migration strategy

1. Create a PostgreSQL schema in parallel
2. Migrate a copy of data
3. Run application tests
4. Compare results between MySQL and PostgreSQL
5. Fix SQL incompatibilities
6. Cut over during a maintenance window or use dual-write/replication if needed

---

If you want to automate it

If you share:

• your MySQL schema
• sample queries
• whether you're using an ORM
• how much data you have

I can help you:

• convert the schema to PostgreSQL
• rewrite MySQL queries to PostgreSQL
• design a migration plan
• generate pgloader config or SQL scripts