MySQL to PostgreSQL Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures and functions, triggers, queries and SQL scripts from MySQL to PostgreSQL (Postgres).

Databases:

Migration Reference

Language Elements

Converting SQL language elements:

MySQL PostgreSQL
1 -- # Single line comment --

Other:

Data Types

Converting character data types:

MySQL PostgreSQL
1 LONGTEXT Character large object, <= 4 Gb TEXT

Converting numeric data types:

MySQL PostgreSQL
1 DECIMAL(p, s) Fixed-point number DECIMAL(p, s)
2 FLOAT(p, s) Single-precision floating point FLOAT(p)
3 INT(m) 32-bit integer INT
4 NUMERIC(p, s) Fixed-point number NUMERIC(p, s)

Converting datetime data types:

MySQL PostgreSQL
1 DATETIME(p) Date and time data with fraction, p <= 6, default is 0 TIMESTAMP(p)
2 TIMESTAMP(p) Date and time data with fraction in UTC , p <= 6, default is 0 TIMESTAMP(p)

Converting binary data types:

MySQL PostgreSQL
1 LONGBLOB Binary large object, <= 4 Gb BYTEA
2 TINYBLOB Binary data, <= 255 bytes BYTEA

Converting other data types:

MySQL PostgreSQL
1 BIT(n) Fixed-length bit string, 1 ⇐ n ⇐ 64 BIT(n)

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

MySQL PostgreSQL
1 AUTO_INCREMENT Identity column GENERATED ALWAYS AS IDENTITY since PostgreSQL 10
Sequence and DEFAULT nextval('seq_name')
2 COLLATE name Removed

Storage and table attributes:

MySQL PostgreSQL
1 AUTO_INCREMENT = start_value table option ALTER SEQUENCE seq_name RESTART WITH start_value
2 ENGINE = engine_name Removed

UPDATE Statement

Converting UPDATE statement:

MySQL PostgreSQL
1 UPDATE t INNER JOIN t2 ON … SET … WHERE Update with join UPDATE t SET … FROM t2 WHERE …

Other SQL Statements

Converting other SQL statements:

MySQL PostgreSQL
1 CREATE DATABASE [IF NOT EXISTS] name CREATE DATABASE name
2 DROP TABLE [IF EXISTS] table DROP TABLE [IF EXISTS] table