MySQL to PostgreSQL Schema Conversion: What Each Column Type Should Become, and What Silently Changes

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Most MySQL types have an obvious PostgreSQL twin, but eight need a decision: TINYINT(1), UNSIGNED integers, DATETIME vs TIMESTAMP, ENUM, SET, ON UPDATE CURRENT_TIMESTAMP, AUTO_INCREMENT and the collation behind every unique text column. The last one fails silently: a MySQL unique email refuses [e-mail] after [e-mail], and the same constraint on PostgreSQL accepts both.

Converting a MySQL schema to PostgreSQL is mostly mechanical, and the parts that are not mechanical are the ones that break production. Eight kinds of column need a decision, because the obvious PostgreSQL type either refuses data MySQL accepted, or accepts data MySQL refused. The worst of them changes no type at all: a unique text column that ignored case in MySQL stops ignoring it.

Every behaviour below was run on MySQL 8.4.11 and PostgreSQL 18.3 in throwaway containers, against this small shop schema:

CREATE TABLE customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY customers_email_key (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
  options SET('gift','express','fragile') NOT NULL DEFAULT '',
  total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The eight MySQL columns that need a decision

Eight kinds of MySQL column change behaviour when translated literally: TINYINT(1), BIGINT UNSIGNED, DATETIME, ENUM, SET, ON UPDATE CURRENT_TIMESTAMP, AUTO_INCREMENT, and unique text columns under a _ci collation.

Most columns translate one to one: DECIMAL(10,2) is NUMERIC(10,2), VARCHAR(255) is VARCHAR(255), INT UNSIGNED fits in bigint, and a MySQL TIMESTAMP is timestamptz. These eight do not:

MySQL	Literal PostgreSQL version	What changes	Decide
TINYINT(1)	boolean	MySQL stored 2 without complaint; boolean refuses any integer	boolean if the column only holds 0 and 1, smallint if not
BIGINT UNSIGNED	bigint	Values above 9,223,372,036,854,775,807 fail with bigint out of range	bigint for keys, numeric(20,0) for hashes and external ids that use the full range
DATETIME	timestamp	Nothing yet, but it keeps no zone, unlike MySQL's TIMESTAMP	timestamptz if the app writes UTC
ENUM(...)	varchar plus CHECK, or CREATE TYPE	The value list moves from the type to a constraint or a named type	One of three designs, chosen on purpose
SET(...)	varchar	MySQL refused unknown members; plain text accepts anything	text[] with a containment CHECK
ON UPDATE CURRENT_TIMESTAMP	nothing	updated_at stops moving	A trigger
AUTO_INCREMENT	GENERATED BY DEFAULT AS IDENTITY	Loaded ids do not move the counter; the next insert collides	setval after the load
A unique text column under a _ci collation	UNIQUE	Case and accents start to count	A lower() index or a nondeterministic collation

The last two get their own sections below.

How do you convert a MySQL schema to PostgreSQL?

Convert the schema first, deciding each of those eight columns yourself, then move the rows with pgloader or COPY.

pgloader can do both in one pass with its own default casts, and they differ from a literal mapping in places that matter: it casts DATETIME to timestamptz, turns each ENUM into a PostgreSQL enum type named after the table and column, casts TINYINT(1) to boolean through its own tinyint-to-boolean function rather than refusing odd values, gives AUTO_INCREMENT keys serial or bigserial, and resets sequences after the load. Its create no tables option instead loads into tables that already exist, which is the route when you want to decide each column yourself and write the CREATE TABLE script first.

Whichever tool writes the script, read it against the list above before running it. These are the cases from the test run where a literal translation accepted the script and then behaved differently.

TINYINT(1): boolean only if the data agrees

MySQL has no real boolean, so BOOLEAN is TINYINT(1) and the (1) is a display width, not a limit. On 8.4, INSERT INTO customers (email, is_active) VALUES ('[e-mail]', 2) succeeded and stored 2. PostgreSQL's boolean refuses the same insert with column "is_active" is of type boolean but expression is of type integer. Before choosing boolean, count the rows that would not fit:

SELECT count(*) FROM customers WHERE is_active NOT IN (0, 1);

Run it before the load whichever tool moves the rows. Zero means boolean. Anything else means some code uses the flag as a small number, and smallint keeps it until that code is found. Changing the column type later is its own migration, covered in changing an integer column to boolean in Postgres.

UNSIGNED integers: one size up, except BIGINT

PostgreSQL has no unsigned integers. An INT UNSIGNED holds up to 4,294,967,295, which fits in bigint, so the safe mapping is one size up: TINYINT UNSIGNED to smallint, SMALLINT UNSIGNED and MEDIUMINT UNSIGNED to integer, INT UNSIGNED to bigint. BIGINT UNSIGNED has nowhere to go. MySQL accepted id = 18446744073709551615; PostgreSQL's bigint stops at 9,223,372,036,854,775,807 and refused it with bigint out of range. For an AUTO_INCREMENT key that will never reach 2^63, bigint is right. For a column holding 64-bit hashes or another system's ids, check SELECT max(col) first and use numeric(20,0) if the top half is in use. Why MySQL schemas are full of UNSIGNED in the first place is in signed vs unsigned primary keys in MySQL.

DATETIME vs TIMESTAMP: only one of them is an instant

The MySQL manual puts the difference in one sentence: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)" In the test, both columns were set to 2026-10-29 09:00:00 in a session at +07:00 and read back at +00:00: the TIMESTAMP returned 02:00:00, the DATETIME still 09:00:00.

So a MySQL TIMESTAMP is an instant and becomes timestamptz without debate. A DATETIME is whatever the application wrote. A literal conversion makes it timestamp, which is correct only for wall-clock values. If the application wrote UTC, which is the usual case, convert it on the PostgreSQL side once the rows are in:

ALTER TABLE customers ALTER COLUMN updated_at TYPE TIMESTAMPTZ(0) USING updated_at AT TIME ZONE 'UTC';

Why the naive type costs you later is in timestamp vs timestamptz.

ENUM and SET: the value list has to live somewhere

A MySQL ENUM becomes either a CHECK constraint on a varchar, a PostgreSQL enum type, or a lookup table. That is a design choice with real trade-offs, and it is the subject of enum vs check constraint vs lookup table. A CHECK is the easiest to change later.

SET is the one that loses a rule quietly. Under MySQL 8.4's default strict sql_mode, INSERT INTO orders (..., options) VALUES (..., 'gift,wrap') failed with Data truncated for column 'options', because wrap is not a member. Converted to a varchar, PostgreSQL stored gift,wrap without a word. Converting the column to an array with a containment check, in place and with its rows, puts the membership rule back:

ALTER TABLE orders ALTER COLUMN options DROP DEFAULT;
ALTER TABLE orders ALTER COLUMN options TYPE TEXT[] USING string_to_array(options, ',');
ALTER TABLE orders ALTER COLUMN options SET DEFAULT '{}';
ALTER TABLE orders ADD CONSTRAINT orders_options_check CHECK (options <@ ARRAY['gift', 'express', 'fragile']);

gift,express became {gift,express}, an empty set became {}, and ARRAY['gift', 'wrap'] then failed on orders_options_check. One MySQL rule does not come back: MySQL stored gift,gift as gift, while the array keeps the duplicate.

ON UPDATE CURRENT_TIMESTAMP: there is no column option to translate

MySQL's updated_at moved on every UPDATE in the test. PostgreSQL has no column clause that does this, so the converted column kept its first value forever, and the UPDATE reported success. A BEFORE UPDATE trigger brings the behaviour back:

CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER customers_set_updated_at BEFORE UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

One function serves every table; each table needs its own trigger. The trigger against the ORM approach, per framework, is in how to auto-update an updated_at column.

AUTO_INCREMENT: identity, and the sequence after the load

Map AUTO_INCREMENT to GENERATED BY DEFAULT AS IDENTITY, then reset each sequence with setval after the rows are loaded, or the next insert collides with a loaded id.

AUTO_INCREMENT keeps a value the row supplies, which is PostgreSQL's GENERATED BY DEFAULT AS IDENTITY, and that is what a load needs, since the rows arrive with their ids. The catch is the counter. After loading ids 1 and 2 into a fresh table, the next insert without an id failed with duplicate key value violates unique constraint "customers_pkey" and Key (id)=(1) already exists, because the identity sequence never saw the loaded rows. One statement per table fixes it:

SELECT setval(pg_get_serial_sequence('customers', 'id'), (SELECT max(id) FROM customers));

pgloader resets the sequences it created unless told reset no sequences; on tables you created yourself, check with SELECT last_value from the sequence, or run the setval anyway. A hand-rolled COPY never does it. Whether to switch to GENERATED ALWAYS once the load is done is covered in serial vs identity in Postgres.

Is a unique constraint case-sensitive in PostgreSQL?

Yes: PostgreSQL compares text exactly, so a unique email that refused [e-mail] after [e-mail] in MySQL accepts both, with no error.

This is the change no type mapping shows. MySQL 8's default collation for utf8mb4 is utf8mb4_0900_ai_ci: accent-insensitive and case-insensitive. Every comparison goes through it, unique keys included. In the test:

On the same schema	MySQL 8.4, utf8mb4_0900_ai_ci	PostgreSQL 18.3
Insert [e-mail], then [e-mail]	Second insert fails: Duplicate entry '[e-mail]' for key 'customers.customers_email_key'	Both rows stored
Insert josé@example.com, then [e-mail]	Second insert fails: duplicate entry	Both rows stored
WHERE email = '[e-mail]'	Finds the row	Finds nothing

The PostgreSQL manual states it plainly: "All standard and predefined collations are deterministic", meaning two strings are equal only if they are the same bytes. So after the conversion, a sign-up form that MySQL protected now creates a second account for the same person, and a login that typed the address in capitals stops finding it. Nothing errors.

There are two ways to keep the old rule:

• A unique index on lower(email), after dropping the plain constraint: CREATE UNIQUE INDEX customers_email_lower_key ON customers (lower(email));. It refused [e-mail] after [e-mail]. It does not make WHERE email = ... case-insensitive, so queries have to say lower(email) too, and it does nothing for accents.
• A nondeterministic ICU collation on the column. CREATE COLLATION ci_ai (provider = icu, locale = 'und-u-ks-level1', deterministic = false) ignores case and accents, as _ai_ci did. With the column set COLLATE ci_ai and the UNIQUE added back, [e-mail] was refused after josé@example.com, and a plain WHERE email = '[e-mail]' found the row. The manual's und-u-ks-level2 variant ignores case but not accents. The cost is that B-tree deduplication cannot be used on such an index.

Not every MySQL column is affected. A column declared COLLATE utf8mb4_bin was already case-sensitive, and only text columns that sit in a primary key, unique constraint or unique index change who gets in. A non-unique column still changes how WHERE matches, which is worth a search through the application's queries.

Converting the schema in Schemity

Schemity writes the PostgreSQL script without changing the diagram, with a report above it that lists every type it changed and the behaviour that does not carry over.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Connect it to the MySQL database, or import a mysqldump --no-data file, and the schema is a diagram in MySQL's own types.

From there, the SQL dialog behind Export > SQL file has a Database dropdown: pick PostgreSQL and the script is rewritten for it, with a conversion report above it. The diagram stays a MySQL diagram, and nothing leaves your machine. For the shop schema the report reads:

-- Converted by Schemity from MySQL to PostgreSQL. The diagram itself is unchanged.
-- Review the notes below before running this script.
--
-- Type mappings:
--   BIGINT UNSIGNED -> BIGINT (2 columns)
--   DATETIME -> TIMESTAMP(0) (1 column)
--   DECIMAL(10,2) -> NUMERIC(10,2) (1 column)
--   ENUM('pending','paid','shipped','cancelled') -> VARCHAR(9) (1 column): Allowed values become a CHECK constraint.
--   INTEGER UNSIGNED -> BIGINT (1 column)
--   SET('gift','express','fragile') -> VARCHAR(21) (1 column): A set of values is stored as comma-separated text, unchecked.
--   TIMESTAMP -> TIMESTAMPTZ(0) (1 column)
--
-- Notes:
--   customers.updated_at: ON UPDATE CURRENT_TIMESTAMP is left out: PostgreSQL has no column that sets itself on update.
--   customers.email: Unique in MySQL under utf8mb4_0900_ai_ci, which ignores case and accents; PostgreSQL compares text exactly, so values that differ only in case and accents are both accepted.
--   2 BIGINT UNSIGNED column(s) hold values up to 2^64-1; PostgreSQL's BIGINT stops at 2^63-1.
--   2 character set or collation setting(s) left out: PostgreSQL picks its own.

The script under it creates is_active as BOOLEAN NOT NULL DEFAULT TRUE, both keys as BIGINT GENERATED BY DEFAULT AS IDENTITY, and status with the CHECK ("status" IN ('pending', 'paid', 'shipped', 'cancelled')). It ran on PostgreSQL 18.3 as written. The report is also saved as a comment block at the top of the file, so whoever runs the script reads the same notes.

The conversion makes the literal choice in every row of the table at the top rather than guessing at your data: TINYINT(1) becomes BOOLEAN, DATETIME stays timestamp, SET stays text, and the email keeps a plain UNIQUE. The report covers most of those choices, each tied to the column it is about. Three are yours to remember: whether a TINYINT(1) really holds only 0 and 1, whether a DATETIME holds UTC, and the setval after the load. Make them in the diagram you are converting into: Import SQL on a PostgreSQL diagram reads the converted script.

From there you design the next one in PostgreSQL's types, change updated_at to TIMESTAMPTZ, add the lower(email) index, and export the final script. The rows still move with pgloader's create no tables or your own COPY, followed by the setval.

MySQL to PostgreSQL, in one list

Most of the conversion is mechanical; these eight columns are not, and each has a short answer.

• TINYINT(1): boolean only after SELECT count(*) ... NOT IN (0, 1) returns zero.
• UNSIGNED: one size up; BIGINT UNSIGNED to bigint for keys, numeric(20,0) if values pass 2^63.
• TIMESTAMP to timestamptz; DATETIME to timestamptz too if it holds UTC, converted with AT TIME ZONE 'UTC'.
• ENUM: a CHECK, an enum type or a lookup table, chosen on purpose. SET: text[] with a <@ check.
• ON UPDATE CURRENT_TIMESTAMP: a BEFORE UPDATE trigger on each table.
• AUTO_INCREMENT: GENERATED BY DEFAULT AS IDENTITY, then setval after the load.
• Every unique text column under a _ci collation: a lower() unique index or a nondeterministic collation, or accept that case now matters.

Choosing a key type while you are redesigning anyway is in UUID vs bigint primary keys, and what changes about indexes when you move a unique rule around is in unique constraint vs unique index.