Convert MySQL to PostgreSQL
Drop a dump from mysqldump, mariadb-dump, mysqlpump, phpMyAdmin or Adminer, as .sql or .sql.gz, and download a PostgreSQL script with the same tables, keys, indexes and data. Every value PostgreSQL can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, 8 fixes, none rejected
Tables in shop_2024.sqlMySQL 8.0.46 dump · written by mysqldump · utf8mb4
customers8 rows3
7 columns id bigint not null name varchar(100) not null email varchar(150) not null country varchar(2) not null is_active boolean not null signed_up date created_at timestamptz(0) not null orders10 rows5
9 columns id bigint not null customer_id bigint not null status text not null total numeric(10,2) not null items integer not null ordered_at timestamp(0) not null shipped_at timestamp(0) note varchar(255) updated_at timestamptz(0) not null
-- Written by IntoSQL from a MySQL dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';
CREATE TABLE customers (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name varchar(100) NOT NULL,
email varchar(150) NOT NULL,
country varchar(2) DEFAULT 'US' NOT NULL,
is_active boolean DEFAULT true NOT NULL,
signed_up date,
created_at timestamptz(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT customers_id_check CHECK (id >= 0)
);
CREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
customer_id bigint NOT NULL,
status text DEFAULT 'pending' NOT NULL,
total numeric(10,2) NOT NULL,
items integer DEFAULT 1 NOT NULL,
ordered_at timestamp(0) NOT NULL,
shipped_at timestamp(0),
note varchar(255),
updated_at timestamptz(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT orders_id_check CHECK (id >= 0),
CONSTRAINT orders_customer_id_check CHECK (customer_id >= 0),
CONSTRAINT orders_status_check CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', '')),
CONSTRAINT orders_items_check CHECK (items >= 0)
);
COPY customers (id, name, email, country, is_active, signed_up, created_at) FROM stdin;
1 Ana Martínez ana@example.com MX t 2023-01-15 2023-01-15 10:24:00+00:00
2 Zoë Müller zoe@example.de DE t 2023-02-03 2023-02-03 08:11:42+00:00
3 José Peña jose@example.es ES f 2023-03-21 2023-03-21 19:05:17+00:00
4 李雷 lilei@example.cn CN t 2023-05-09 2023-05-09 02:40:00+00:00
5 Noah O'Brien noah@example.ie IE t \N 2023-06-30 14:00:00+00:00
6 Amélie Laurent amelie@example.fr FR t 2024-01-02 2024-01-02 09:30:00+00:00
7 Ravi Patel ravi@example.in IN f 2024-02-29 2024-02-29 23:59:59+00:00
8 Sofia Rossi 🌻 sofia@example.it IT t 2024-04-12 2024-04-12 12:12:12+00:00
\.
COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, updated_at) FROM stdin;
1 1 shipped 129.90 3 2024-03-01 10:00:00 2024-03-02 16:30:00 \N 2024-03-02 16:30:00+00:00
2 2 paid 54.00 1 2024-03-02 11:15:00 \N Gift wrap 🎁 2024-03-02 11:20:00+00:00
3 1 cancelled 12.50 1 2024-03-03 09:45:00 \N Customer changed their mind 2024-03-03 10:00:00+00:00
4 4 shipped 980.00 12 2024-03-05 02:10:00 2024-03-07 08:00:00 请小心轻放 2024-03-07 08:00:00+00:00
5 5 pending 45.75 2 2024-03-08 17:20:00 \N Leave at the door; ring twice 2024-03-08 17:20:00+00:00
6 6 shipped 310.40 5 2024-03-10 13:00:00 2024-03-11 09:00:00 Fragile: "glass" 2024-03-11 09:00:00+00:00
7 8 paid 76.20 2 2024-03-11 18:45:00 \N \N 2024-03-11 18:46:00+00:00
8 2 19.99 1 2024-03-12 08:00:00 \N Status lost in an old import 2024-03-12 08:00:00+00:00
9 3 shipped 64.00 2 2024-03-14 15:30:00 \N Shipping date never recorded 2024-03-15 10:00:00+00:00
10 7 paid 1499.00 1 2024-03-15 21:05:00 \N C:\\Users\\ravi\\invoice.pdf 2024-03-15 21:05:00+00:00
\.
-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX customers_uq_email ON customers (email);
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX orders_idx_customer ON orders (customer_id);
CREATE INDEX orders_idx_status_date ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;
ALTER TABLE customers ADD CONSTRAINT customers_uq_email UNIQUE USING INDEX customers_uq_email;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
DO $f$ BEGIN
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
EXCEPTION WHEN foreign_key_violation THEN
RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'fk_orders_customer';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1001), 9223372036854775807)::bigint, false);
COMMENT ON TABLE customers IS 'People who bought at least once';
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 5001), 9223372036854775807)::bigint, false);
CREATE FUNCTION orders_updated_at_on_update() RETURNS trigger LANGUAGE plpgsql AS $f$
BEGIN
IF NEW IS DISTINCT FROM OLD AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at THEN
NEW.updated_at := CURRENT_TIMESTAMP;
END IF;
RETURN NEW;
END
$f$;
CREATE TRIGGER orders_updated_at_on_update BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION orders_updated_at_on_update();
Fixes
- "id" of customers is unsigned, so it became bigint with a check that it isn't negative.
- "signed_up" of customers allows NULL now: some of its values couldn't be stored and are NULL.
- The default of "signed_up" in customers, 0000‑00‑00, isn't a date PostgreSQL accepts, so the column has no default.
- "customer_id" of orders is unsigned, so it became bigint with a check that it isn't negative.
- "id" of orders is unsigned, so it became bigint with a check that it isn't negative.
- "items" of orders is unsigned, so it became integer with a check that it isn't negative.
- 1 row of "status" in orders holds the empty value MySQL stores for invalid entries; the check of its values allows it.
- "updated_at" of orders changes to the current time on every update; a trigger does that in PostgreSQL.
Needs a look
- 1 value in "signed_up" of customers is a zero or impossible date such as 0000‑00‑00, which PostgreSQL can't store, so it is NULL. If those dates mean something, replace them in the source with real ones before dumping.
- 1 value in "shipped_at" of orders is a zero or impossible date such as 0000‑00‑00 00:00:00, which PostgreSQL can't store, so it is NULL. If those dates mean something, replace them in the source with real ones before dumping.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from MySQL to PostgreSQL
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
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
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.
Report line: 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.
Report line: on_update_trigger
generated columns
STORED
PostgreSQL 13 to 17 only store generated columns; the formula is the same.
Report line: 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.
Report line: generated_not_translated
INVISIBLE columns
regular columns
PostgreSQL has no invisible columns.
Report line: 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.
Report line: check_skipped
CHECK ... NOT ENFORCED
not created
MySQL keeps these checks without applying them, so the rows may break them.
Report line: check_not_enforced
defaults with functions PostgreSQL lacks
no default
A default that can't be written for PostgreSQL is left out and reported.
Report line: 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.
Report line: prefix_unique
FULLTEXT indexes
GIN on to_tsvector('simple', ...)
Full-text search works with @@ instead of MATCH ... AGAINST.
Report line: 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.
Report line: index_on_wide_values
SPATIAL indexes
not created without PostGIS
Spatial indexes need PostGIS.
Report line: 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.
Report line: 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.
Report line: foreign_key_skipped
table and column names
lowercase when that stays unique
Lowercase names can be written without quotes in PostgreSQL.
Report line: identifier_renamed
names over 63 bytes
shortened with a hash
PostgreSQL names hold 63 bytes; the hash keeps shortened names apart.
Report line: name_shortened
GEOMETRY, POINT, POLYGON...
WKB in bytea
Without PostGIS, the standard well-known binary keeps every geometry exactly.
Report line: 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.
Report line: partitions_dropped
WITH SYSTEM VERSIONING (MariaDB)
a table with the current rows
A dump holds the current rows only.
Report line: system_versioning_dropped
MyISAM, MEMORY and other engines
a regular table
PostgreSQL has one table engine.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: geometry_inferred
_ci collations
exact comparison
PostgreSQL compares text exactly unless told otherwise.
Report line: 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.
Report line: 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.
Report line: charset_guessed
sjis, cp932, gbk, big5 dumps
read as UTF-8, with a warning
These encodings are not decoded yet.
Report line: 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.
Report line: expression_value
DEFAULT in VALUES
the column's default value
A literal default is copied into the row.
Report line: default_keyword_filled
DEFAULT in VALUES without a literal default
NULL
There is no value to copy.
Report line: default_keyword_null
REPLACE and INSERT IGNORE
plain rows
A dump has one row per key, so the rows are the same.
Report line: replace_as_insert
types IntoSQL doesn't know
text
Text holds any value; change the type after loading.
Report line: 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.
Report line: invalid_value_null
unreadable geometries
NULL
Bytes that aren't a geometry can't be stored as one.
Report line: bad_geometry
CREATE SEQUENCE (MariaDB)
CREATE SEQUENCE with its current value
PostgreSQL sequences take the same options.
| In MySQL | In PostgreSQL | Report line |
|---|---|---|
| TINYINT(1), BOOL | boolean when every value is 0 or 1, else smallintMySQL'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 | droppedZero 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 scalePostgreSQL 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) | NULLPostgreSQL 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 droppedA 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 defaultA 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 intervalMySQL'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 rangePostgreSQL 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 | timestampA 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 noneA 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 valuesText 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 checkMySQL 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 CHECKValues 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 listAn array keeps each element separate, so queries can test membership. | |
| JSON | jsonb; json when a value holds \u0000jsonb 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 | removedPostgreSQL 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 characterPostgreSQL text must be valid UTF-8. | 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 |
| AUTO_INCREMENT | GENERATED BY DEFAULT AS IDENTITY, with the counter set past the copied idsIdentity 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 tableMyISAM 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 triggerThe 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 | STOREDPostgreSQL 13 to 17 only store generated columns; the formula is the same. | virtual_as_stored |
| generated columns with functions PostgreSQL lacks | a plain columnA formula that can't be written for PostgreSQL leaves a plain column and a report line. | generated_not_translated |
| INVISIBLE columns | regular columnsPostgreSQL has no invisible columns. | invisible_column |
| CHECK constraints | translated, inline in CREATE TABLEInline checks are tested as each row loads instead of in a scan per check afterwards. | |
| CHECK constraints with functions PostgreSQL lacks | not createdA check that can't be written for PostgreSQL is left out and reported. | check_skipped |
| CHECK ... NOT ENFORCED | not createdMySQL keeps these checks without applying them, so the rows may break them. | check_not_enforced |
| defaults with functions PostgreSQL lacks | no defaultA 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 | hashA btree entry can't hold values that long; a hash index still finds equal values. | index_on_wide_values |
| SPATIAL indexes | not created without PostGISSpatial indexes need PostGIS. | spatial_index_skipped |
| functional indexes PostgreSQL can't write | not createdAn 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 orphansDumps 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 createdPostgreSQL needs a unique key on the referenced columns; InnoDB doesn't. | foreign_key_skipped |
| table and column names | lowercase when that stays uniqueLowercase names can be written without quotes in PostgreSQL. | identifier_renamed |
| names over 63 bytes | shortened with a hashPostgreSQL names hold 63 bytes; the hash keeps shortened names apart. | name_shortened |
| GEOMETRY, POINT, POLYGON... | WKB in byteaWithout PostGIS, the standard well-known binary keeps every geometry exactly. | geometry_as_wkb |
| INET4, INET6, UUID (MariaDB) | inet, uuidPostgreSQL has the same types. | |
| BINARY, VARBINARY, BLOB | byteaBytes stay bytes. | |
| PARTITION BY | one tableThe 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 rowsA dump holds the current rows only. | system_versioning_dropped |
| MyISAM, MEMORY and other engines | a regular tablePostgreSQL has one table engine. | engine_dropped |
| views, triggers, procedures, functions and events | listed in the reportTheir 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 valuesA 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 byteaMySQL's geometry format is recognised from the values and stored as standard WKB. | geometry_inferred |
| _ci collations | exact comparisonPostgreSQL compares text exactly unless told otherwise. | case_insensitive_collation |
| SET NAMES utf8 from utf8mb4 tables | converted, with a warningMySQL 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 warningThese encodings are not decoded yet. | unsupported_charset |
| UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows | evaluatedSome 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 | NULLA converter that doesn't run a database can't evaluate them. | expression_value |
| DEFAULT in VALUES | the column's default valueA literal default is copied into the row. | default_keyword_filled |
| DEFAULT in VALUES without a literal default | NULLThere is no value to copy. | default_keyword_null |
| REPLACE and INSERT IGNORE | plain rowsA dump has one row per key, so the rows are the same. | replace_as_insert |
| types IntoSQL doesn't know | textText holds any value; change the type after loading. | unknown_type |
| values that don't fit their column | NULLA damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads. | invalid_value_null |
| unreadable geometries | NULLBytes that aren't a geometry can't be stored as one. | bad_geometry |
| CREATE SEQUENCE (MariaDB) | CREATE SEQUENCE with its current valuePostgreSQL sequences take the same options. |
Load the script into PostgreSQL
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.
psql -v ON_ERROR_STOP=1 -d mydb -f mysql.postgresql.sqlForeign keys are added as NOT VALID and then validated. If a row points to a row that doesn't exist, the script tells you which constraint, instead of failing half way.
For a large dump, create the database first and run the script with psql from the same machine as the server.
Dump MySQL for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- --default-character-set=utf8mb4 keeps emoji and other 4-byte characters, which a utf8 dump loses before the converter sees it.
- --hex-blob writes binary columns as hexadecimal, which survives copying between systems.
- --routines --triggers --events add those objects to the dump, so the report can list them.
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 name, never dropped silently, so you can rewrite them for PostgreSQL.
Measured against pgloader
The same dumps, converted and loaded by IntoSQL and by pgloader 3.6.7 on one machine (a 10-core i9-10900K with a SATA SSD), in October 2026.
- A TINYINT(1) column holding 2, -1 and -5: pgloader loads true for each one, without a warning. IntoSQL keeps the numbers and the report says why.
- A dump with a default of 'it's', a table named with a backtick, an ENUM holding the empty value and a TIME over 24 hours: pgloader stops. IntoSQL converts it and loads it.
- What pgloader loads has lost its primary, unique and foreign keys and its identities. IntoSQL keeps every one.
- A 5.3 GB dump of 30 million rows: pgloader needs it restored into a running MySQL first, which takes 18 minutes, then runs out of memory with its defaults. IntoSQL converts the dump in 10 seconds, and psql loads the script in about 3 minutes.
- Across 476 dumps from MySQL 5.5 to 9 and MariaDB 10.3 to 12, loaded into PostgreSQL 13 and 18: no load errors, and no value changed without a line in the report.
Questions about MySQL to PostgreSQL
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 MariaDB dumps?
Yes. Dumps from mariadb-dump and from MariaDB 10.3 to 12 convert the same way, including MariaDB's INET6, INET4 and UUID columns.
What happens to 0000-00-00 dates?
PostgreSQL has no zero date, so they become NULL and the column accepts NULL. The report says how many there were in each column.
Are AUTO_INCREMENT values kept?
Yes. Each AUTO_INCREMENT column becomes an identity column, every row keeps its id, and the next id continues after the highest one, or after the table's AUTO_INCREMENT if that is higher.
Does TINYINT(1) become boolean?
Only when every value in the column is 0 or 1. If it holds other numbers, such as 2 or -1, it stays a number and the report says why, so no value turns into true by accident.
What about stored procedures and triggers?
They are listed in the report by name and not converted, because each database writes them in its own language. ON UPDATE CURRENT_TIMESTAMP is the exception: it becomes a PostgreSQL trigger.
Which PostgreSQL versions does the script load on?
PostgreSQL 13 to 18. The same script loads on each of them, stopping at the first error if anything goes wrong.