Skip to content
IntoSQL home
Convert a file

Data types

PostgreSQL to MySQL data types

Every PostgreSQL data type that MySQL stores differently, what the converter writes for it, when, and the report line.

View as Markdown

Each row is a type from PostgreSQL that MySQL stores differently: what the MySQL script gets, why, and the line the report writes about it. The rows come from the converter's own rules, so this page matches what it does.

Convert in the browser: PostgreSQL to MySQL opens the converter set up for this pair. The PostgreSQL to MySQL migration guide covers dumping, loading and the rules for keys, indexes and names.

A report line is a fix when the converter changed something and lists it, or a problem when a value or object is worth a look.

Numbers

In PostgreSQL In MySQL Why Report line
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. decimal_as_text (fix)
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 (fix)

Booleans and bits

In PostgreSQL In MySQL Why Report line
boolean tinyint(1) MySQL's BOOLEAN is TINYINT(1): true is 1 and false is 0. None
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. None

Text

In PostgreSQL In MySQL Why Report line
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. None
text that isn't valid in its encoding the bad bytes as the replacement character Text has to be valid utf8mb4. invalid_utf8_replaced (problem)

Enums and sets

In PostgreSQL In MySQL Why Report line
enum types enum(...) with the same labels MySQL's ENUM keeps the labels in their order. None

Dates and times

In PostgreSQL In MySQL Why Report line
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. type_replaced (fix)
timestamp datetime(p) A timestamp without time zone is a wall-clock reading, like datetime. None
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. moments_as_utc (fix)
time time(p) MySQL's TIME holds every time of day, 24:00:00 included. None
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. date_out_of_range (problem)

Binary

In PostgreSQL In MySQL Why Report line
bytea blob, mediumblob or longblob; varbinary(3072) in a key Bytes stay bytes. None

JSON, spatial and other types

In PostgreSQL In MySQL Why Report line
arrays (integer[], text[]...) json arrays MySQL has no arrays. A JSON array keeps every item and NULL, and JSON functions read it. type_replaced (fix)
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. type_replaced (fix)
json, jsonb json The same JSON values; jsonb's key order and spacing are the server's. None
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. invalid_value_null (problem)
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. None
types IntoSQL doesn't know longtext Text holds any value; change the type after loading. unknown_type (problem)

Counters and identities

In PostgreSQL In MySQL Why Report line
serial, identity AUTO_INCREMENT, continuing after the highest id The column that leads the primary key counts; the counter continues where the sequence was. None
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. counter_dropped (problem)

Other pairs