Convert MySQL data types to PostgreSQL: the full mapping table, the pre-flight query for every risky type, and the conversions that succeed and change your data

How do I convert MySQL data types to PostgreSQL?

Take the mapping table below as the starting point, then override it for the columns where the two engines genuinely disagree. Most converters, pgloader included, ship a default casting ruleset that handles the bulk correctly. The work is not writing the mapping, it is deciding which defaults to reject. There are roughly a dozen of those, and every one of them produces valid DDL and a successful load, which is exactly why they survive testing.

The framing that helps is to sort types into three buckets. Bucket one is identical or near enough: DECIMAL, FLOAT, DOUBLE, BLOB, TEXT, DATE, JSON. Bucket two is a mechanical promotion because PostgreSQL lacks a size that MySQL has: TINYINT, MEDIUMINT and every unsigned variant. Bucket three is where the engines actually disagree about meaning: TINYINT(1), TIMESTAMP, ENUM, SET, YEAR and anything touching collation. Bucket three is the whole job.

MySQL to PostgreSQL data type mapping table

Every row checked against the MySQL 8.4 Reference Manual and the PostgreSQL manual on 31 August 2026.

What is the PostgreSQL equivalent of MySQL TINYINT?

smallint, because PostgreSQL has no one byte integer type. The complication is TINYINT(1) specifically. The pgloader reference documents a default casting rule that converts TINYINT to boolean when the precision is 1, and most other converters do something similar, because TINYINT(1) is the conventional way to store a flag in MySQL. That convention is not enforced by anything.

The MySQL manual is explicit on this point. Display width, it says, "does not constrain the range of values that can be stored in the column", and it gives SMALLINT(3) as an example of a column that still holds the full -32768 to 32767 range. So a column declared TINYINT(1) can hold any value from -128 to 127, and a converter reading that 1 as a promise about the data is reading a formatting hint. The same manual page also states that display width for integer types is deprecated and that support for it should be expected to be removed in a future version of MySQL. The convention the rule depends on is being withdrawn by the vendor whose feature it is.

The fix is one query, and it belongs in your migration checklist rather than in a postmortem: run SELECT col, COUNT(*) FROM t GROUP BY col on the source. If the result is two rows, convert to boolean with confidence. If it is more, convert to smallint and let the application decide what the values mean.

Does PostgreSQL have unsigned integers?

No. PostgreSQL has smallint, integer and bigint, and all three are signed. There is no unsigned variant and no flag to make one, so every unsigned MySQL column has to be promoted to the next size up. TINYINT UNSIGNED becomes smallint, SMALLINT UNSIGNED becomes integer, INT UNSIGNED becomes bigint. Those three are mechanical and pgloader does them by default.

BIGINT UNSIGNED is the one with no clean answer. MySQL documents its maximum as 18,446,744,073,709,551,615. PostgreSQL bigint stops at 9,223,372,036,854,775,807, which is slightly under half of it, and there is no larger integer type to promote into. The correct target is numeric(20,0), which is exact and can hold the range, at the cost of being variable length and slower to index than a native integer.

In practice most BIGINT UNSIGNED columns never come close to the ceiling, and mapping them to bigint works fine for years. That is what makes it dangerous rather than safe: the failure is deferred to whichever row first crosses 2 to the power of 63, which will happen long after anyone remembers the decision. Run SELECT MAX(col) once, write the answer in the migration notes, and pick deliberately.

How do I convert MySQL DATETIME to PostgreSQL?

DATETIME maps to timestamp without time zone, and that is correct because neither engine does anything clever with a DATETIME. The mistake is sending MySQL TIMESTAMP to the same place. MySQL converts a TIMESTAMP value to UTC when it stores it and converts it back to the session time zone when it reads it. DATETIME gets no such treatment. Two columns that look identical in a schema dump behave differently, and only TIMESTAMP has any business becoming timestamptz.

The second issue is zero dates. MySQL 8.4 enables NO_ZERO_DATE and NO_ZERO_IN_DATE by default, so a fresh install will not accept 0000-00-00. Databases that have been running since before that default, or that had strict mode turned off to get an old application working, are a different matter. With strict mode disabled, the MySQL manual states that invalid dates such as 2004-04-31 are converted to 0000-00-00 and a warning is generated. Those rows are sitting in production right now, and PostgreSQL has no such value.

pgloader has a rule for this, but read what it actually covers: it fires when a column's DEFAULT is the zero date, converting the type to timestamptz, dropping the not null and dropping the default. A column with an ordinary default that happens to contain zero dates in its rows is not covered. Count them first with SELECT COUNT(*) FROM t WHERE col < '1000-01-01' and decide whether those rows become NULL or get corrected before the load.

What is the PostgreSQL equivalent of MySQL ENUM?

Two reasonable answers, and one common wrong one. PostgreSQL has a native enum type created with CREATE TYPE, which is the closest structural match and is efficient to store. The alternative is a text column with a CHECK constraint listing the allowed values, which is easier to alter later because adding a value to a PostgreSQL enum requires ALTER TYPE and removing one is genuinely awkward. Most teams migrating an application schema are happier with text plus CHECK.

The wrong answer is plain varchar with no constraint, which is what you get by default from several tools. Every existing value survives, the load succeeds, and the validation that made the column worth declaring as an ENUM is gone. The allowed value list is not lost though: MySQL keeps it in information_schema.COLUMNS.COLUMN_TYPE, so you can generate the CHECK constraints mechanically from the source before you convert anything.

SET is the harder sibling and has no equivalent at all. MySQL stores a SET as a comma joined string and lets you query membership with FIND_IN_SET. Mapping it to varchar keeps it looking right in a SELECT and turns every membership filter into a LIKE against a substring, which is both slow and wrong at the boundaries. A junction table is the migration that leaves you with a schema worth having. text[] with a GIN index is the migration that leaves you with less work today.

Why does my MySQL to PostgreSQL migration have duplicate rows?

Almost always collation, and this catches teams who did everything else right. MySQL 8.4 ships utf8mb4 as the default character set with utf8mb4_0900_ai_ci as its default collation. The suffix is the whole story: ai means accent insensitive and ci means case insensitive. Under that collation, MySQL considers [email protected] and [email protected] to be the same value, so a unique index on an email column rejected the second one.

PostgreSQL's default collation is neither accent nor case insensitive. The same unique index, recreated faithfully on the target, now permits both spellings. Nothing fails during the migration, because the existing data was already unique under the stricter comparison. The duplicates arrive later, from ordinary application traffic, as two accounts for one person or two SKUs for one product.

Detect it before you migrate with SELECT COUNT(*), COUNT(DISTINCT LOWER(col)) FROM t. If the numbers match, that column is relying on case insensitive uniqueness and you need to reproduce it on the PostgreSQL side, either with a unique index on lower(col) or with the citext extension. The same difference changes sort order, so reports come back in a different sequence with no code change, and it changes the result of every equality comparison on a text column in application SQL.

How do I handle AUTO_INCREMENT in PostgreSQL?

Use a GENERATED BY DEFAULT AS IDENTITY column, which is the SQL standard spelling and what modern PostgreSQL prefers over serial. pgloader handles the declaration automatically: its default rules convert an int with the auto_increment extra to serial, and a larger one to bigserial. The declaration is not the problem.

The problem is the sequence's current value. A freshly created identity column starts at 1. If you then load ten million existing rows with their original primary keys, every one of those rows is correct, every foreign key resolves, every count reconciles, and the sequence still thinks the next value is 1. The migration passes every test you can run against static data. The first insert after go-live fails on a duplicate primary key, and so does the second, and so on for ten million attempts.

The fix is to run setval on every sequence to the current maximum as the final step of the cutover, after the last row has arrived rather than before. Then insert one row per table and roll it back, as a smoke test that costs a minute and catches the sequence you missed. Before you change any of this, it is worth knowing which reports, jobs and downstream tables actually read the columns you are about to retype, because tracing what depends on a column is usually faster than finding out from whoever notices first.

How do I verify a MySQL to PostgreSQL type conversion?

Run the checks on MySQL before the load, not on PostgreSQL after it. Every problem described above is a cheap query against the source and an expensive incident in production. This is the list we work through, and it takes about an hour on a schema of any size because most of it is generated from information_schema.

After the load, reconcile on values rather than counts. For each table compare the row count, the SUM of every numeric column, the MIN and MAX of every date column, and a hash of the primary keys. Row counts alone reconcile perfectly on a migration that has flattened every boolean and rounded every money column, which is why a green count report is the single most misleading artifact in this kind of project.

One last piece of scope that belongs in the plan rather than in a surprise: the pgloader reference states that views are not migrated and triggers are not migrated, because supporting them would require parsing the full SQL dialect. That is an honest limitation rather than a defect, and it applies to most tools in this category. Whatever your converter does with types, the views, triggers and stored routines are hand work.

The full tool comparison for this route, including what each one bills by, is on our MySQL to PostgreSQL migration tools page, and the step by step cutover sequence is in the MySQL to Postgres migration guide. The equivalent type mapping for the other two big routes into PostgreSQL is in SQL Server to PostgreSQL migration and Oracle to PostgreSQL migration tools. If MySQL is not actually switching off, the ongoing case is covered in Postgres ETL tools, and the budget model behind either program is in what a data migration really costs.