Skip to content
IntoSQL home
Convert a file

Data types

MariaDB to PostgreSQL data types

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

View as Markdown

Each row is a type from MariaDB that PostgreSQL stores differently: what the PostgreSQL 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: MariaDB to PostgreSQL opens the converter set up for this pair. The MariaDB to PostgreSQL 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 MariaDB In PostgreSQL Why Report line
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 (fix)
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 (fix)
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 (fix)

Booleans and bits

In MariaDB 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 (fix)
BIT(1), BIT(n) boolean, bit(n) A single bit is a yes or no; wider bit fields keep every bit. None

Text

In MariaDB In PostgreSQL Why Report line
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. None
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 (problem)
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 (problem)

Enums and sets

In MariaDB In PostgreSQL Why Report line
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. None
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 (fix)
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 (problem)
SET text[] with a CHECK that every element is in the list An array keeps each element separate, so queries can test membership. None

Dates and times

In MariaDB In PostgreSQL Why Report line
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 (problem)
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 (fix)
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 (problem)
YEAR smallint with a CHECK of its range PostgreSQL has no year type; the check keeps the same values MySQL allows. None
DATETIME timestamp A DATETIME is a wall-clock reading without a time zone, like PostgreSQL's timestamp. None
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 (problem)

Binary

In MariaDB In PostgreSQL Why Report line
BINARY, VARBINARY, BLOB bytea Bytes stay bytes. None

JSON, spatial and other types

In MariaDB In PostgreSQL Why Report line
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 (fix)
GEOMETRY, POINT, POLYGON... WKB in bytea Without PostGIS, the standard well-known binary keeps every geometry exactly. geometry_as_wkb (fix)
INET4, INET6, UUID (MariaDB) inet, uuid PostgreSQL has the same types. None
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 (fix)
types IntoSQL doesn't know text Text holds any value; change the type after loading. unknown_type (problem)
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 (problem)
unreadable geometries NULL Bytes that aren't a geometry can't be stored as one. bad_geometry (problem)
VECTOR(n) (MariaDB 11.7+) jsonb with the floats as an array PostgreSQL has no vector type without pgvector; a JSON array keeps every float, and casting it to pgvector's vector after loading reads the same text. type_replaced (fix)

Counters and identities

In MariaDB In PostgreSQL Why Report line
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. None
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 (problem)

Other pairs