Convert PostgreSQL to MySQL
Drop a plain dump from pg_dump or pg_dumpall, as .sql or .sql.gz, and download a script for MySQL with the same tables, keys, indexes and data. Every value MySQL can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, 3 fixes, none rejected
Tables in shop_2024_postgres.sqlPostgreSQL 18.6 dump · written by pg_dump · utf8mb4
customers8 rows2
8 columns id int not null name text not null email varchar(768) not null country char(2) not null is_active tinyint(1) not null tags json not null signed_up date created_at datetime(6) not null orders10 rows1
10 columns id int not null customer_id int not null status enum('pending','paid','shipped','cancelled') not null total decimal(10,2) not null items smallint not null ordered_at datetime(6) not null shipped_at datetime(6) note text details json delivery varchar(100)
Not converted
- View paid_orders
Each database writes these in its own language. Rewrite them for MySQL.
-- Written by IntoSQL from a PostgreSQL dump. Load it with: mysql --default-character-set=utf8mb4 <database> < <file>
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES';
CREATE TABLE `customers` (
`id` int NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`email` varchar(768) NOT NULL,
`country` char(2) NOT NULL DEFAULT 'US',
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`tags` json NOT NULL,
`signed_up` date,
`created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
UNIQUE KEY `customers_email_key` (`email`),
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='People who bought at least once';
CREATE TABLE `orders` (
`id` int NOT NULL AUTO_INCREMENT,
`customer_id` int NOT NULL,
`status` enum('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
`total` decimal(10,2) NOT NULL,
`items` smallint NOT NULL DEFAULT 1,
`ordered_at` datetime(6) NOT NULL,
`shipped_at` datetime(6),
`note` text,
`details` json,
`delivery` varchar(100),
PRIMARY KEY (`id`),
KEY `idx_orders_customer` (`customer_id`),
KEY `idx_orders_status_date` (`status`,`ordered_at`),
CONSTRAINT `orders_total_check` CHECK (`total` >= (0)),
CONSTRAINT `orders_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`tags`,`signed_up`,`created_at`) VALUES (1,'Ana Martínez','ana@example.com','MX',1,'["vip"]','2023-01-15','2023-01-15 10:24:00'),(2,'Zoë Müller','zoe@example.de','DE',1,'[]','2023-02-03','2023-02-03 08:11:42'),(3,'José Peña','jose@example.es','ES',0,'["wholesale","late payer"]','2023-03-21','2023-03-21 19:05:17'),(4,'李雷','lilei@example.cn','CN',1,'[]','2023-05-09','2023-05-09 02:40:00'),(5,'Noah O\'Brien','noah@example.ie','IE',1,'[]',NULL,'2023-06-30 14:00:00'),(6,'Amélie Laurent','amelie@example.fr','FR',1,'["vip"]','2024-01-02','2024-01-02 09:30:00'),(7,'Ravi Patel','ravi@example.in','IN',0,'[]','2024-02-29','2024-02-29 23:59:59'),(8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'[]','2024-04-12','2024-04-12 12:12:12');
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`details`,`delivery`) VALUES (1,1,'shipped',129.90,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,'{"gift": false}','1 day 06:30:00'),(2,2,'paid',54.00,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁','{"gift": true, "wrap": "red"}',NULL),(3,1,'cancelled',12.50,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind',NULL,NULL),(4,4,'shipped',980.00,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放','{"fragile": true}','2 days 05:50:00'),(5,5,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door; ring twice',NULL,NULL),(6,6,'shipped',310.40,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"','{"fragile": true}','20:00:00'),(7,8,'paid',76.20,2,'2024-03-11 18:45:00',NULL,NULL,NULL,NULL),(8,2,'shipped',19.99,1,'2024-03-12 08:00:00',NULL,'Shipped date set to infinity by an old import',NULL,NULL),(9,3,'shipped',64.00,2,'2024-03-14 15:30:00','2024-03-15 10:00:00','Two parcels','{"parcels": [1, 2]}','18:30:00'),(10,7,'paid',1499.00,1,'2024-03-15 21:05:00',NULL,'C:\\Users\\ravi\\invoice.pdf',NULL,NULL);
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
Fixes
- "created_at" of customers holds moments with a time zone; MySQL has no type that keeps one, so they are stored in UTC as datetime(6).
- "tags" of customers is text[], which MySQL doesn't have, so it is json with the same values.
- "delivery" of orders is interval, which MySQL doesn't have, so it is varchar(100) with the same values.
Needs a look
- 1 view, trigger, routine or event isn't converted; the report lists each one. Recreate them by hand in MySQL.
- The default of "tags" in customers can't be written for MySQL (a value its type can't hold), so the column has no default. Add the default by hand if new rows need it.
- The index idx_orders_details on orders uses an expression that can't be written for MySQL (neither MySQL 5.7 nor MariaDB index an expression), so it isn't created. Create the index by hand if queries need it.
- 1 value in "shipped_at" of orders is a date MySQL can't store, such as infinity, so it is NULL. MySQL stores years 1 to 9999 without infinity; replace those dates in the source if they matter.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from PostgreSQL to MySQL
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
boolean
tinyint(1)
MySQL's BOOLEAN is TINYINT(1): true is 1 and false is 0.
varchar(n), char(n), text
varchar(n) up to 1024 characters, char(n) up to 255, text beyond
Text keeps every character as utf8mb4. A column in a key is varchar(768) at most, the longest a key holds, and so is text with a default, which MySQL 5.7 gives only to varchar.
numeric(p,s) past 65 digits or 30 decimals, and numeric values past what decimal(65,30) holds
varchar
MySQL's decimal holds 65 digits with 30 decimals. Wider numbers are kept as text so every digit stays exact; numeric without a precision is decimal(65,30) when its values fit.
Report line: decimal_as_text
money
decimal(19,2)
MySQL has no money type. The amount is kept as a number, without the currency symbol the server's locale added.
Report line: type_replaced
interval
varchar(100) with PostgreSQL's text
MySQL's TIME holds durations up to 838 hours, but an interval has months and days; its text keeps every part.
Report line: type_replaced
arrays (integer[], text[]...)
json arrays
MySQL has no arrays. A JSON array keeps every item and NULL, and JSON functions read it.
Report line: type_replaced
ranges, geometric types, tsvector, xml, hstore, composite and extension types
longtext with PostgreSQL's text
MySQL has no equivalent type, so each value keeps the text PostgreSQL wrote.
Report line: type_replaced
json, jsonb
json
The same JSON values; jsonb's key order and spacing are the server's.
timestamp
datetime(p)
A timestamp without time zone is a wall-clock reading, like datetime.
timestamp with time zone
datetime(p) in UTC
MySQL's TIMESTAMP only reaches 2038, so moments are stored as datetime in UTC, the same instant whatever offset the dump wrote.
Report line: moments_as_utc
time
time(p)
MySQL's TIME holds every time of day, 24:00:00 included.
infinity, -infinity, BC dates and years past 9999
NULL
MySQL stores years 1000 to 9999 without infinity. Those values become NULL and the report counts them.
Report line: date_out_of_range
NaN and Infinity in numeric, real and double precision
NULL
MySQL's numbers have no NaN or Infinity, so those values become NULL and the report counts them.
Report line: invalid_value_null
NOT NULL columns with values that become NULL
NOT NULL dropped
A column whose values had to become NULL can't keep NOT NULL, or the load would stop.
Report line: not_null_relaxed
enum types
enum(...) with the same labels
MySQL's ENUM keeps the labels in their order.
uuid, inet, cidr
uuid as char(36), inet and cidr as varchar(43)
MySQL has no uuid or address types; the text keeps every value.
bytea
blob, mediumblob or longblob; varbinary(3072) in a key
Bytes stay bytes.
bit(n)
bit(n) up to 64, text beyond
A bit string up to 64 bits is the number its bits make; a longer one keeps its digits.
citext and nondeterministic collations
exact comparison
Tables use utf8mb4_bin so text compares exactly, as PostgreSQL's does; columns that ignored case in the source lose that.
Report line: case_insensitive_collation
serial, identity
AUTO_INCREMENT, continuing after the highest id
The column that leads the primary key counts; the counter continues where the sequence was.
a second serial or identity column in a table
plain integer
MySQL counts one column per table; any other keeps its values without a counter.
Report line: counter_dropped
sequences not owned by a column
not created
MySQL has no sequences. Columns that count with one become AUTO_INCREMENT instead.
Report line: sequence_not_converted
generated columns
generated columns, STORED as in the source
MySQL computes them the same way.
generated columns with functions MySQL lacks
a plain column
A formula that can't be written for MySQL leaves a plain column and a report line.
Report line: generated_not_translated
defaults with functions MySQL lacks, and defaults from a sequence
no default
A default that can't be written for MySQL is left out and reported.
Report line: default_not_translated
CHECK constraints
translated, inline in CREATE TABLE
MySQL 8.0.16 and MariaDB enforce them; MySQL 5.7 keeps them without checking.
CHECK constraints with functions MySQL lacks
not created
A check that can't be written for MySQL is left out and reported.
Report line: check_skipped
CHECK ... NOT VALID
not created
PostgreSQL never checked the rows that were there before, so they may break it.
Report line: check_not_valid
gin, gist, brin and hash indexes, partial and expression indexes
not created
MySQL has b-tree, full-text and spatial indexes only, on whole columns, without a WHERE.
Report line: index_skipped
foreign keys
foreign keys, loaded with FOREIGN_KEY_CHECKS=0
The rows load in any order, and the keys check new rows afterwards.
foreign keys to tables the dump doesn't create
not created
A key can't name a table that isn't there.
Report line: foreign_key_skipped
partitioned tables
one table with every partition's rows
The rows are the same; partition the table again in MySQL if it needs it.
Report line: partitions_dropped
schemas
one database, tables named after their schema first
MySQL's schemas are databases, which a script loaded into one database can't create; tables in public keep their names.
Report line: schemas_merged
views, functions, procedures, triggers, rules, policies, extensions and types
listed in the report
Their code is written in PostgreSQL's language, so they are listed for you to recreate.
Report line: not_converted_objects
dumps without CREATE TABLE (--data-only)
types read from the values
A data-only dump has only rows, so the types come from the values.
Report line: no_schema
expressions in INSERT values
NULL
A converter that doesn't run a database can't evaluate them.
Report line: expression_value
types IntoSQL doesn't know
longtext
Text holds any value; change the type after loading.
Report line: unknown_type
text that isn't valid in its encoding
the bad bytes as the replacement character
Text has to be valid utf8mb4.
Report line: invalid_utf8_replaced
text decoded twice (ñ)
copied as it is
The dump holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected
| In PostgreSQL | In MySQL | Report line |
|---|---|---|
| boolean | tinyint(1)MySQL's BOOLEAN is TINYINT(1): true is 1 and false is 0. | |
| varchar(n), char(n), text | varchar(n) up to 1024 characters, char(n) up to 255, text beyondText keeps every character as utf8mb4. A column in a key is varchar(768) at most, the longest a key holds, and so is text with a default, which MySQL 5.7 gives only to varchar. | |
| numeric(p,s) past 65 digits or 30 decimals, and numeric values past what decimal(65,30) holds | varcharMySQL's decimal holds 65 digits with 30 decimals. Wider numbers are kept as text so every digit stays exact; numeric without a precision is decimal(65,30) when its values fit. | decimal_as_text |
| money | decimal(19,2)MySQL has no money type. The amount is kept as a number, without the currency symbol the server's locale added. | type_replaced |
| interval | varchar(100) with PostgreSQL's textMySQL's TIME holds durations up to 838 hours, but an interval has months and days; its text keeps every part. | type_replaced |
| arrays (integer[], text[]...) | json arraysMySQL has no arrays. A JSON array keeps every item and NULL, and JSON functions read it. | type_replaced |
| ranges, geometric types, tsvector, xml, hstore, composite and extension types | longtext with PostgreSQL's textMySQL has no equivalent type, so each value keeps the text PostgreSQL wrote. | type_replaced |
| json, jsonb | jsonThe same JSON values; jsonb's key order and spacing are the server's. | |
| timestamp | datetime(p)A timestamp without time zone is a wall-clock reading, like datetime. | |
| timestamp with time zone | datetime(p) in UTCMySQL's TIMESTAMP only reaches 2038, so moments are stored as datetime in UTC, the same instant whatever offset the dump wrote. | moments_as_utc |
| time | time(p)MySQL's TIME holds every time of day, 24:00:00 included. | |
| infinity, -infinity, BC dates and years past 9999 | NULLMySQL stores years 1000 to 9999 without infinity. Those values become NULL and the report counts them. | date_out_of_range |
| NaN and Infinity in numeric, real and double precision | NULLMySQL's numbers have no NaN or Infinity, so those values become NULL and the report counts them. | invalid_value_null |
| NOT NULL columns with values that become NULL | NOT NULL droppedA column whose values had to become NULL can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| enum types | enum(...) with the same labelsMySQL's ENUM keeps the labels in their order. | |
| uuid, inet, cidr | uuid as char(36), inet and cidr as varchar(43)MySQL has no uuid or address types; the text keeps every value. | |
| bytea | blob, mediumblob or longblob; varbinary(3072) in a keyBytes stay bytes. | |
| bit(n) | bit(n) up to 64, text beyondA bit string up to 64 bits is the number its bits make; a longer one keeps its digits. | |
| citext and nondeterministic collations | exact comparisonTables use utf8mb4_bin so text compares exactly, as PostgreSQL's does; columns that ignored case in the source lose that. | case_insensitive_collation |
| serial, identity | AUTO_INCREMENT, continuing after the highest idThe column that leads the primary key counts; the counter continues where the sequence was. | |
| a second serial or identity column in a table | plain integerMySQL counts one column per table; any other keeps its values without a counter. | counter_dropped |
| sequences not owned by a column | not createdMySQL has no sequences. Columns that count with one become AUTO_INCREMENT instead. | sequence_not_converted |
| generated columns | generated columns, STORED as in the sourceMySQL computes them the same way. | |
| generated columns with functions MySQL lacks | a plain columnA formula that can't be written for MySQL leaves a plain column and a report line. | generated_not_translated |
| defaults with functions MySQL lacks, and defaults from a sequence | no defaultA default that can't be written for MySQL is left out and reported. | default_not_translated |
| CHECK constraints | translated, inline in CREATE TABLEMySQL 8.0.16 and MariaDB enforce them; MySQL 5.7 keeps them without checking. | |
| CHECK constraints with functions MySQL lacks | not createdA check that can't be written for MySQL is left out and reported. | check_skipped |
| CHECK ... NOT VALID | not createdPostgreSQL never checked the rows that were there before, so they may break it. | check_not_valid |
| gin, gist, brin and hash indexes, partial and expression indexes | not createdMySQL has b-tree, full-text and spatial indexes only, on whole columns, without a WHERE. | index_skipped |
| foreign keys | foreign keys, loaded with FOREIGN_KEY_CHECKS=0The rows load in any order, and the keys check new rows afterwards. | |
| foreign keys to tables the dump doesn't create | not createdA key can't name a table that isn't there. | foreign_key_skipped |
| partitioned tables | one table with every partition's rowsThe rows are the same; partition the table again in MySQL if it needs it. | partitions_dropped |
| schemas | one database, tables named after their schema firstMySQL's schemas are databases, which a script loaded into one database can't create; tables in public keep their names. | schemas_merged |
| views, functions, procedures, triggers, rules, policies, extensions and types | listed in the reportTheir code is written in PostgreSQL's language, so they are listed for you to recreate. | not_converted_objects |
| dumps without CREATE TABLE (--data-only) | types read from the valuesA data-only dump has only rows, so the types come from the values. | no_schema |
| expressions in INSERT values | NULLA converter that doesn't run a database can't evaluate them. | expression_value |
| types IntoSQL doesn't know | longtextText holds any value; change the type after loading. | unknown_type |
| text that isn't valid in its encoding | the bad bytes as the replacement characterText has to be valid utf8mb4. | invalid_utf8_replaced |
| text decoded twice (ñ) | copied as it isThe dump holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |
Load the script into MySQL
The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.
mysql --default-character-set=utf8mb4 mydb < postgresql.mysql.sqlForeign keys are created as the dump declares them, with FOREIGN_KEY_CHECKS=0 while the rows load, so a row that points to a missing row doesn't stop the load.
For a large dump, run the client on the same machine as the server, and raise max_allowed_packet if a single row is very large.
IntoSQL Pro loads the file straight into MySQL from the desktop app or the command line, without writing a script.
Dump PostgreSQL for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- --format=plain, pg_dump's default, writes SQL. A custom or directory archive (-Fc, -Fd) becomes SQL with pg_restore -f dump.sql, without a database server.
- --no-owner --no-privileges leave out roles and grants, which the converter skips anyway, so the dump is smaller.
- pg_dumpall dumps every database in one file; each one's tables are named after it, so nothing collides.
What isn't converted
Views, materialized views, functions, procedures, triggers, rules, row security policies, extensions and types are written in PostgreSQL's own language. They are listed in the report by name, never dropped silently, so you can rewrite them for MySQL.
Questions about PostgreSQL to MySQL
Not answered here? Write to us from the contact page and a person replies. Contact page
Is my dump uploaded?
No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.
How large can the dump be?
The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.
Does it read pg_dump -Fc archives?
Not directly: a custom archive is a binary format. pg_restore -f dump.sql dump.backup turns it into the plain SQL the converter reads, without connecting to a server. The converter says so when it gets an archive.
Which PostgreSQL versions can the dump come from?
Dumps written by pg_dump and pg_dumpall from PostgreSQL 10 on, with COPY or INSERT rows. Every test dump comes from PostgreSQL 13 and 18 and is compared with its source cell by cell.
Are serial and identity values kept?
Yes, for one column per table: the serial or identity column that leads the primary key becomes AUTO_INCREMENT and continues after the highest id. MySQL counts one column per table, so any other keeps its values without a counter, and the report says so.
What happens to arrays, enums and other PostgreSQL types?
Arrays become JSON arrays, booleans tinyint(1), enums ENUM with the same labels, and money a decimal. Ranges, geometric types, hstore and composite types keep PostgreSQL's text, and the report says which columns.
What about functions, triggers and views?
They are listed in the report by name and not converted, because each database writes them in its own language.
Which MySQL versions does the script load on?
MySQL 5.7 and later. Every test dump loads on 5.7 and 9, stopping at the first error if anything goes wrong.