Databases
MySQL to PostgreSQL
Convert a MySQL or MariaDB dump into a PostgreSQL script with the same tables, keys, indexes and data: how to dump, convert, load, and every rule the converter applies.
IntoSQL reads a MySQL or MariaDB dump and writes one PostgreSQL script with the same tables, columns, keys, indexes, foreign keys, defaults, identities, comments and rows. It reads the dump itself, without a database server, and lists in the report every value PostgreSQL can't hold as it is and every object it doesn't carry. The script loads on PostgreSQL 13 to 18.
Dump for the best result
Any dump from mysqldump, mariadb-dump, mysqlpump, phpMyAdmin or Adminer works, as .sql or .sql.gz. These options keep more of your data:
mysqldump --default-character-set=utf8mb4 --hex-blob \
--routines --triggers --events --single-transaction \
shop | gzip > shop.sql.gz--default-character-set=utf8mb4keeps emoji and other 4-byte characters. A dump made withutf8has already lost them, and the report says so (dump_lost_four_byte_chars).--hex-blobwrites binary columns as hexadecimal, which survives copying between systems.--routines --triggers --eventsadd those objects to the dump, so the report can list them by name.--single-transactiontakes a consistent snapshot of InnoDB tables without locking them.
A dump made with --no-create-info still converts: the types are read from the values, and the report says the schema was missing (no_schema).
Convert
In the browser, open MySQL to PostgreSQL or the converter on the home page and drop the dump. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.
With the command line:
intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to postgresql --output shop.postgresql.sql --report shop.report.jsoninspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.
Through the API, a dump converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "postgresql"}.
Load into PostgreSQL
createdb shop
psql -v ON_ERROR_STOP=1 -d shop -f shop.postgresql.sqlThe script creates the tables without keys, copies the rows with COPY, then adds keys, indexes, foreign keys and identities, so it loads fast and stops at the first error. Foreign keys are added as NOT VALID and then validated: a row that points to a missing row is named by its constraint instead of failing the load half way. Each identity continues after the highest id copied, or after the table's AUTO_INCREMENT when that is higher.
What isn't converted
Views, triggers, stored procedures, functions and events are written in each database's own language. They are listed in the report by kind and name, never dropped silently. ON UPDATE CURRENT_TIMESTAMP is the exception: it becomes a PostgreSQL trigger.
Every rule
Generated from the engine, where each rule is decided.
| In MySQL | In PostgreSQL | Why | Report line |
|---|---|---|---|
| TINYINT(1), BOOL | boolean when every value is 0 or 1, else smallint | MySQL's BOOL is a TINYINT(1) that can hold any number from -128 to 127. A column that only holds 0 and 1 becomes boolean; one that holds 2 or -1 stays a number so no value changes meaning. | bool_kept_numeric |
| UNSIGNED integers | the next wider integer with a CHECK that it isn't negative; BIGINT UNSIGNED is bigint when every value fits, else numeric(20,0) | PostgreSQL has no unsigned integers. A wider type holds the whole unsigned range, and the check keeps negative values out as MySQL did. | unsigned_widened |
| ZEROFILL and display widths | dropped | Zero padding is a way of showing a number, not part of it. The numbers are kept; format them when you read them if the padding matters. | zerofill_dropped |
| FLOAT(M,D) | real or double precision, without the scale | PostgreSQL floats have no fixed scale. The stored values are copied as they are. | float_scale_dropped |
| BIT(1), BIT(n) | boolean, bit(n) | A single bit is a yes or no; wider bit fields keep every bit. | |
| zero, partial or impossible dates (0000-00-00, 2024-02-30) | NULL | PostgreSQL only stores real calendar dates. MySQL keeps zero and impossible dates when its SQL mode allows them; they become NULL and the report counts them. | invalid_date_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. | not_null_relaxed |
| DEFAULT '0000-00-00' | no default | A default PostgreSQL can't store is left out rather than replaced with a made-up date. | zero_date_default |
| TIME | time when every value is within a day, else interval | MySQL's TIME is a duration from -838:59:59 to 838:59:59. A column whose values fit in a day stays a time of day; one with longer or negative values becomes interval. | time_as_interval |
| YEAR | smallint with a CHECK of its range | PostgreSQL has no year type; the check keeps the same values MySQL allows. | |
| CHAR(n) | varchar(n) | MySQL removes the trailing spaces of CHAR values when it reads them, and PostgreSQL's char(n) would add them back, so varchar(n) keeps the values as MySQL returns them. | |
| DATETIME | timestamp | A DATETIME is a wall-clock reading without a time zone, like PostgreSQL's timestamp. | |
| TIMESTAMP | timestamptz in the time zone the dump states, UTC when it states none | A TIMESTAMP is a moment. Dumps state the zone they wrote it in (mysqldump writes UTC); without that, the values are read as UTC and the report says so. | timestamp_zone_assumed |
| ENUM | text with a CHECK of its values | Text with a check keeps the same values and is easier to change than a PostgreSQL enum type. | |
| ENUM holding '' (MySQL's error value) | the empty value allowed by the check | MySQL stores an empty string when an invalid value goes into an ENUM in a lenient SQL mode. Those rows are kept and the check allows them. | enum_invalid_value |
| ENUM or SET holding values outside its list | no CHECK | Values the list doesn't name would fail the check, so the check isn't created. | enum_check_skipped |
| SET | text[] with a CHECK that every element is in the list | An array keeps each element separate, so queries can test membership. | |
| JSON | jsonb; json when a value holds \u0000 | jsonb is the type PostgreSQL indexes and queries well, but it rejects the \u0000 escape, so a column with it stays json. | json_not_jsonb |
| NUL bytes in text | removed | PostgreSQL text can't hold a NUL byte. The byte is removed and the report counts the values. | nul_removed |
| text that isn't valid in its character set | the bad bytes as the replacement character | PostgreSQL text must be valid UTF-8. | 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. | mojibake_suspected |
| AUTO_INCREMENT | GENERATED BY DEFAULT AS IDENTITY, with the counter set past the copied ids | Identity columns accept the copied ids, and the counter continues after the highest one or the dump's AUTO_INCREMENT, whichever is higher. | |
| MyISAM AUTO_INCREMENT on the second column of a key | one counter for the table | MyISAM counts per group of the key's first column; PostgreSQL has one counter per column. | myisam_group_autoincrement |
| ON UPDATE CURRENT_TIMESTAMP | a BEFORE UPDATE trigger | The trigger sets the column to the current time when a row changes and the update didn't set it, as MySQL does. | on_update_trigger |
| generated columns | STORED | PostgreSQL 13 to 17 only store generated columns; the formula is the same. | virtual_as_stored |
| generated columns with functions PostgreSQL lacks | a plain column | A formula that can't be written for PostgreSQL leaves a plain column and a report line. | generated_not_translated |
| INVISIBLE columns | regular columns | PostgreSQL has no invisible columns. | invisible_column |
| CHECK constraints | translated, inline in CREATE TABLE | Inline checks are tested as each row loads instead of in a scan per check afterwards. | |
| CHECK constraints with functions PostgreSQL lacks | not created | A check that can't be written for PostgreSQL is left out and reported. | check_skipped |
| CHECK ... NOT ENFORCED | not created | MySQL keeps these checks without applying them, so the rows may break them. | check_not_enforced |
| defaults with functions PostgreSQL lacks | no default | A default that can't be written for PostgreSQL is left out and reported. | default_not_translated |
| keys and indexes on a prefix, c(20) | an index on left(c, 20) | PostgreSQL indexes expressions, so the same prefix is indexed. | prefix_unique |
| FULLTEXT indexes | GIN on to_tsvector('simple', ...) | Full-text search works with @@ instead of MATCH ... AGAINST. | fulltext_as_gin |
| indexes on values over 2,000 bytes | hash | A btree entry can't hold values that long; a hash index still finds equal values. | index_on_wide_values |
| SPATIAL indexes | not created without PostGIS | Spatial indexes need PostGIS. | spatial_index_skipped |
| functional indexes PostgreSQL can't write | not created | An index on an expression that can't be written for PostgreSQL is left out and reported. | index_skipped |
| foreign keys | after the data, NOT VALID, then validated with a warning on orphans | Dumps load with FOREIGN_KEY_CHECKS=0 and MyISAM never checks, so rows without a parent are common. The key is created first and validated last; orphans leave it NOT VALID with a warning instead of stopping the load. | |
| foreign keys to columns without a unique key | not created | PostgreSQL needs a unique key on the referenced columns; InnoDB doesn't. | foreign_key_skipped |
| table and column names | lowercase when that stays unique | Lowercase names can be written without quotes in PostgreSQL. | identifier_renamed |
| names over 63 bytes | shortened with a hash | PostgreSQL names hold 63 bytes; the hash keeps shortened names apart. | name_shortened |
| GEOMETRY, POINT, POLYGON... | WKB in bytea | Without PostGIS, the standard well-known binary keeps every geometry exactly. | geometry_as_wkb |
| INET4, INET6, UUID (MariaDB) | inet, uuid | PostgreSQL has the same types. | |
| BINARY, VARBINARY, BLOB | bytea | Bytes stay bytes. | |
| PARTITION BY | one table | The rows are the same; partition the table again in PostgreSQL if it needs it. | partitions_dropped |
| WITH SYSTEM VERSIONING (MariaDB) | a table with the current rows | A dump holds the current rows only. | system_versioning_dropped |
| MyISAM, MEMORY and other engines | a regular table | PostgreSQL has one table engine. | engine_dropped |
| views, triggers, procedures, functions and events | listed in the report | Their code is written in MySQL's language, so they are listed for you to recreate. | not_converted_objects |
| dumps without CREATE TABLE | types read from the values | A dump made with --no-create-info only has rows, so the types come from the values. | no_schema |
| geometries in a dump without CREATE TABLE | WKB in bytea | MySQL's geometry format is recognised from the values and stored as standard WKB. | geometry_inferred |
| _ci collations | exact comparison | PostgreSQL compares text exactly unless told otherwise. | case_insensitive_collation |
| SET NAMES utf8 from utf8mb4 tables | converted, with a warning | MySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file. | dump_lost_four_byte_chars |
| dumps without SET NAMES whose text isn't UTF-8 | read as Latin-1 (Windows-1252) | MySQL's latin1 is Windows-1252, the usual encoding of such dumps. | charset_guessed |
| sjis, cp932, gbk, big5 dumps | read as UTF-8, with a warning | These encodings are not decoded yet. | unsupported_charset |
| UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows | evaluated | Some dump tools write values as these calls; they are read as the values they produce, with geographic WKT turned to longitude first. | |
| other expressions in rows | NULL | A converter that doesn't run a database can't evaluate them. | expression_value |
| DEFAULT in VALUES | the column's default value | A literal default is copied into the row. | default_keyword_filled |
| DEFAULT in VALUES without a literal default | NULL | There is no value to copy. | default_keyword_null |
| REPLACE and INSERT IGNORE | plain rows | A dump has one row per key, so the rows are the same. | replace_as_insert |
| types IntoSQL doesn't know | text | Text holds any value; change the type after loading. | unknown_type |
| values that don't fit their column | NULL | A damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads. | invalid_value_null |
| unreadable geometries | NULL | Bytes that aren't a geometry can't be stored as one. | bad_geometry |
| CREATE SEQUENCE (MariaDB) | CREATE SEQUENCE with its current value | PostgreSQL sequences take the same options. |