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:

MySQL 8.x and 5.x
PostgreSQL 16.x, 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x

Migration Reference

Language Elements

Converting SQL language elements:

	MySQL			PostgreSQL
1	--	#	Single line comment	--

Other:

Constraint Name Issue - "Relation Already Exists" Error

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

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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference