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

> 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 `Ana@Example.com` after `ana@example.com`, and the same constraint on PostgreSQL accepts both.

Source: https://schemity.com/blog/mysql-to-postgresql-schema-conversion/

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:

```sql
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](https://pgloader.readthedocs.io/en/latest/ref/mysql.html) 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 ('flag@example.com', 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:

```sql
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](https://schemity.com/blog/postgres-change-integer-column-to-boolean/).

### 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](https://schemity.com/blog/mysql-unsigned-vs-signed-primary-key/).

### DATETIME vs TIMESTAMP: only one of them is an instant

The [MySQL manual](https://dev.mysql.com/doc/refman/8.4/en/datetime.html) 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:

```sql
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](https://schemity.com/blog/postgres-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](https://schemity.com/blog/postgres-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:

```sql
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:

```sql
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](https://schemity.com/blog/auto-update-updated-at-column-mysql-postgres-orm/).

## 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:

```sql
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](https://schemity.com/blog/postgres-serial-vs-identity/).

## Is a unique constraint case-sensitive in PostgreSQL?

Yes: PostgreSQL compares text exactly, so a unique email that refused `Ana@Example.com` after `ana@example.com` 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 `ana@example.com`, then `Ana@Example.com` | Second insert fails: `Duplicate entry 'Ana@Example.com' for key 'customers.customers_email_key'` | Both rows stored |
| Insert `josé@example.com`, then `jose@example.com` | Second insert fails: duplicate entry | Both rows stored |
| `WHERE email = 'ANA@EXAMPLE.COM'` | Finds the row | Finds nothing |

The [PostgreSQL manual](https://www.postgresql.org/docs/current/collation.html) 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 `Ana@Example.com` after `ana@example.com`. 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, `JOSE@example.com` was refused after `josé@example.com`, and a plain `WHERE email = 'Jose@Example.com'` 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](https://schemity.com/doc/connect-mysql/), or import a `mysqldump --no-data` file, and the schema is a diagram in MySQL's own types.

![Schemity's canvas reading the shop schema from MySQL: orders has id as an unsigned INTEGER marked auto, customer_id as an unsigned BIGINT foreign key, options as SET with an empty default, status as ENUM with the default pending underlined, and total as DECIMAL(10,2); customers has id as an unsigned BIGINT marked auto, email as VARCHAR(255) with the U unique marker, is_active as BOOLEAN defaulting to TRUE, created_at as TIMESTAMP and updated_at as DATETIME, both defaulting to NOW(); a relationship line runs from one customer to many orders](https://schemity.com/images/blog/mysql-to-postgresql-schema-conversion/canvas-shop-mysql.webp)

From there, the SQL dialog behind **Export > SQL file** has a **Database** dropdown: pick PostgreSQL and the script is [rewritten for it](https://schemity.com/doc/export-sql-ddl/), 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.

![Schemity's Diagram SQL dialog on the MySQL shop diagram converted to PostgreSQL: the conversion report lists INTEGER UNSIGNED to BIGINT, SET('gift','express','fragile') to VARCHAR(21) stored as comma-separated text, unchecked, and TIMESTAMP to TIMESTAMPTZ(0), with notes that customers.updated_at loses ON UPDATE CURRENT_TIMESTAMP and that customers.email was unique in MySQL under utf8mb4_0900_ai_ci, which ignores case and accents; below it the 33-line script repeats the report as comments and creates customers and orders with identity keys, the status CHECK and the options VARCHAR(21), with Copy and Save SQL file buttons](https://schemity.com/images/blog/mysql-to-postgresql-schema-conversion/sql-convert-report-postgresql.webp)

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](https://schemity.com/doc/reverse-engineer-database/) on a PostgreSQL diagram reads the converted script.

![Schemity's canvas after the converted script was imported into a PostgreSQL diagram: orders has id as BIGINT marked auto, customer_id as a BIGINT foreign key, options as VARCHAR(21) with an empty default, status as VARCHAR(9) with the default pending underlined, and total as NUMERIC(10,2), with cc: 1 and idx: 1 in its footer; customers has id as BIGINT marked auto, email as VARCHAR(255) with the U unique marker, is_active as BOOLEAN defaulting to TRUE, created_at as TIMESTAMPTZ(0) and updated_at as TIMESTAMP(0), both defaulting to NOW()](https://schemity.com/images/blog/mysql-to-postgresql-schema-conversion/canvas-shop-postgresql.webp)

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](https://schemity.com/blog/postgres-uuid-vs-bigint-primary-key/), and what changes about indexes when you move a unique rule around is in [unique constraint vs unique index](https://schemity.com/blog/postgres-unique-constraint-vs-unique-index/).

## Frequently asked questions

### Can I convert a MySQL schema to PostgreSQL automatically?

The types, yes: a converter or pgloader maps `INT` to `integer`, `DECIMAL` to `numeric` and gives an `AUTO_INCREMENT` key a sequence without help. What no converter can decide for you is meaning: whether a `TINYINT(1)` holds only 0 and 1, whether a `DATETIME` holds UTC, and whether a unique email should still ignore case. Those need a person who knows the data, so a good conversion lists them instead of guessing.

### Is a unique constraint case-sensitive in PostgreSQL?

Yes. PostgreSQL's standard collations are deterministic, so `Ana@Example.com` and `ana@example.com` are different values and a `UNIQUE` on the column accepts both. MySQL's default collations end in `_ci` and ignore case, so the same constraint there refuses the second row. To keep MySQL's behaviour, add a unique index on `lower(email)` or give the column a nondeterministic ICU collation.

### Should a MySQL DATETIME become timestamp or timestamptz in PostgreSQL?

`timestamptz` if the application writes UTC into it, which most do, and plain `timestamp` only if it holds wall-clock readings such as a store's opening time. MySQL's `TIMESTAMP` is different: it is already converted to UTC for storage, so it maps to `timestamptz` either way.