Copy a Table from MySQL to PostgreSQL (3 Methods)

To copy a table from MySQL to PostgreSQL, create the table in PostgreSQL with matching column types, export the rows from MySQL as tab-separated text, and load the file with psql's \copy. pgloader can do the same in one command, and some GUI clients can copy a table between connections directly. Whichever method you use, finish by resetting the identity sequence and comparing row counts and checksums on both sides.

We make dbPlus, one of the GUI options below, so read that part with that in mind. We tested the export, load, sequence and checksum commands on MySQL 8.4 and PostgreSQL 17 with a small sample table; the syntax is the same on PostgreSQL 18. The pgloader section follows the project's documentation.

Map the column types first

MySQL and PostgreSQL types mostly line up, but a few differ in ways that break a load or quietly change your data.

MySQL	PostgreSQL	Notes
`TINYINT(1)`, `BOOL`	`boolean`	Values 0 and 1 load directly; PostgreSQL accepts them as boolean input
`TINYINT`	`smallint`	PostgreSQL has no 1-byte integer
`SMALLINT UNSIGNED`, `MEDIUMINT`	`integer`
`INT UNSIGNED`	`bigint`	Values above 2,147,483,647 do not fit `integer`
`BIGINT UNSIGNED`	`numeric(20,0)`	Or `bigint` if no value reaches 2^63
`INT AUTO_INCREMENT`	`integer GENERATED BY DEFAULT AS IDENTITY`	Use `bigint` for unsigned ids
`DECIMAL(p,s)`	`numeric(p,s)`
`FLOAT`, `DOUBLE`	`real`, `double precision`
`DATETIME`	`timestamp`	Neither stores a time zone
`TIMESTAMP`	`timestamptz`	MySQL converts it to the session time zone when you read it
`'0000-00-00'` zero dates	`NULL`	PostgreSQL rejects zero dates
`YEAR`	`smallint`
`ENUM('a','b')`	`text` with a `CHECK`, or `CREATE TYPE ... AS ENUM`
`SET('a','b')`	`text[]`	Split the comma-separated value
`JSON`	`jsonb`
`VARCHAR(n)`	`varchar(n)` or `text`	`n` counts characters in both
`TINYTEXT` up to `LONGTEXT`	`text`
`BLOB` types	`bytea`

Use GENERATED BY DEFAULT AS IDENTITY rather than ALWAYS while you migrate. Both let \copy load the existing ids, but ALWAYS rejects them in a normal INSERT, which gets in the way of fixes after the load.

Zero dates such as '0000-00-00 00:00:00' are allowed in older MySQL data, and PostgreSQL has no equivalent. Convert them to NULL during the export (shown below) and make the PostgreSQL column nullable, or pick a real sentinel date if the application cannot handle NULL.

For text, create the PostgreSQL database with UTF8 encoding; it stores every character utf8mb4 can, including emoji. Export with utf8mb4 as the character set; a client connection that uses latin1 turns emoji into question marks or garbled text. PostgreSQL text cannot contain the NUL byte (0x00), which MySQL allows, so strip it if your data has it. Comparisons change too: MySQL's default utf8mb4_0900_ai_ci collation ignores case and accents, while PostgreSQL compares exactly, so WHERE email = 'Ana@Example.com' stops matching ana@example.com. Add an index on lower(email) and query with lower(), or use the citext extension, for columns that relied on case-insensitive matching.

The example table

This MySQL table has most of the awkward types:

CREATE TABLE customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  plan ENUM('free','pro','team') NOT NULL DEFAULT 'free',
  prefs JSON NULL,
  note TEXT NULL,
  balance DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  last_login TIMESTAMP NULL
) DEFAULT CHARSET=utf8mb4;

The PostgreSQL version, following the table above:

CREATE TABLE customers (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email      varchar(255) NOT NULL,
  is_active  boolean NOT NULL DEFAULT true,
  plan       text NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'team')),
  prefs      jsonb,
  note       text,
  balance    numeric(10,2) NOT NULL DEFAULT 0,
  created_at timestamp,
  last_login timestamptz
);

created_at is nullable here because some rows held zero dates.

Method 1: export a file and load it with \copy

This method needs only the mysql and psql clients.

Export with SELECT ... INTO OUTFILE

If you can reach the MySQL server's file system, let the server write the file:

SET time_zone = '+00:00';

SELECT id, email, is_active, plan, prefs, note, balance,
       IF(created_at < '1000-01-01', NULL, created_at),
       last_login
INTO OUTFILE '/var/lib/mysql-files/customers.tsv'
CHARACTER SET utf8mb4
FROM customers
ORDER BY id;

With no FIELDS or LINES clause, MySQL writes tab-separated columns, puts a backslash in front of tabs, newlines and backslashes inside values, and writes NULL as \N. PostgreSQL's COPY reads that text format by default. One caveat: the COPY reference says a backslash followed by a real newline might not be accepted in future releases, so check text columns that contain line breaks. SET time_zone makes TIMESTAMP columns come out in UTC.

The IF(created_at < '1000-01-01', ...) expression turns zero dates into NULL. The more obvious NULLIF(created_at, '0000-00-00 00:00:00') failed in our test on MySQL 8.4, with its default strict SQL mode, with Incorrect DATETIME value.

INTO OUTFILE needs the FILE privilege, can only write inside the directory named by secure_file_priv, and refuses to overwrite an existing file (MySQL manual). The file lands on the database server, so copy it to your Mac with scp afterwards. Avoid the CSV variant with ESCAPED BY '"': in our test it wrote NULL values as "N, which no loader will read back correctly.

Export with the mysql client instead

On a managed database you usually cannot reach the server's disk. The mysql client can write the file on your side:

mysql -h 127.0.0.1 -P 3306 -u app -p --default-character-set=utf8mb4 \
  --batch --quick --skip-column-names \
  -e "SET time_zone = '+00:00';
      SELECT id, email, is_active, plan, prefs, note, balance,
             IF(created_at < '1000-01-01', NULL, created_at), last_login
      FROM customers ORDER BY id" shop > customers.tsv

--batch writes tab-separated rows and escapes tabs, newlines and backslashes. --quick streams rows instead of holding the whole result in memory. The catch is that batch mode prints NULL as the word NULL, exactly like a text value 'NULL'. Before you rely on it, check that no text column contains that string, for example with SELECT COUNT(*) FROM customers WHERE note = 'NULL';.

If the database sits behind a bastion, open a tunnel first as described in connecting to a database through an SSH tunnel on Mac.

Load with \copy

In psql, connected to the target database:

SET TIME ZONE 'UTC';
\copy customers FROM 'customers.tsv'

For the file from the mysql client, tell PostgreSQL how nulls look:

\copy customers FROM 'customers.tsv' WITH (FORMAT text, NULL 'NULL')

\copy reads the file on your Mac and streams it to the server. The SQL command COPY ... FROM 'file' reads a file on the database server instead and needs extra privileges. SET TIME ZONE 'UTC' matters for the timestamptz column: it tells PostgreSQL that the exported times are in UTC.

You can also skip the file and pipe one client into the other:

mysql -h 127.0.0.1 -u app -p --default-character-set=utf8mb4 --batch --quick --skip-column-names \
  -e "SET time_zone = '+00:00'; SELECT ... FROM customers ORDER BY id" shop \
  | PGTZ=UTC psql -h 127.0.0.1 -U app -d app -c "\copy customers FROM pstdin WITH (FORMAT text, NULL 'NULL')"

pstdin is psql's own standard input, which here is the output of mysql.

Method 2: pgloader

pgloader reads the MySQL schema, creates matching tables in PostgreSQL, converts types and copies the data. On a Mac, brew install pgloader installs it; Homebrew had version 3.6.10 when we checked in October 2026. The simplest form copies a whole database:

pgloader mysql://app@127.0.0.1/shop postgresql://app@127.0.0.1/app

For a single table and control over types, write a load file, here customers.load:

LOAD DATABASE
     FROM mysql://migrator@127.0.0.1:3306/shop
     INTO postgresql://app@127.0.0.1:5432/app

 WITH include no drop, create tables, create indexes, reset sequences, no foreign keys

 CAST type datetime to timestamp drop not null using zero-dates-to-null

 INCLUDING ONLY TABLE NAMES MATCHING 'customers'

 ALTER SCHEMA 'shop' RENAME TO 'public';

Run it with pgloader customers.load. The MySQL reference page lists the options and default casting rules. A few worth knowing:

pgloader creates the tables in a PostgreSQL schema named after the MySQL database, because a MySQL database is what PostgreSQL calls a schema (background from pgloader's author). The ALTER SCHEMA ... RENAME TO 'public' line puts them in public instead. To keep the separate schema, drop that line and run ALTER DATABASE app SET search_path TO shop, public; after the load.
Its defaults already turn TINYINT(1) into boolean, most unsigned integers into a larger type and AUTO_INCREMENT into serial or bigserial. DATETIME becomes timestamptz by default, which is why the load file adds a CAST rule for timestamp.
zero-dates-to-null converts zero dates, and text columns go through remove-null-characters, which strips NUL bytes.
reset sequences sets each sequence past the highest imported id, so you can skip the setval step below.
Passwords can go in the URL (double any @ in them), or in MYSQL_PWD for the MySQL side and PGPASSWORD or ~/.pgpass for PostgreSQL.

One limitation matters on current MySQL versions. pgloader 3.6 cannot log in to accounts that use caching_sha2_password, the default authentication plugin since MySQL 8.0 (issue 782). A migration account created WITH mysql_native_password has worked for some users on MySQL 8.0. MySQL 8.4 disables that plugin by default and MySQL 9.0 removed it. The maintainer closed the issue in June 2026, saying the problem is gone in pgloader v4, which uses MySQL's JDBC driver and needs Java 21 or newer, but v4 was still a development build when we checked.

Method 3: a GUI database client

Some desktop clients copy a table from one connection to another, even across engines, without any files. Check which column types the client creates, whether it carries over indexes and defaults, and how it treats zero dates. Our lists of MySQL clients and PostgreSQL GUI clients for Mac compare the options.

dbPlus, our native Mac client, can copy tables between databases, including from MySQL 8.0 or newer to PostgreSQL 12 or newer, and also exports and imports CSV, Excel and JSON. Copying between databases is part of dbPlus Premium, at USD 19 a year through the Mac App Store with a one-week free trial. dbPlus is coming to the Mac App Store and is not released yet. Whichever client you use, run the checks below afterwards.

After the copy: reset the sequence

If you created the table yourself and loaded explicit ids, the identity sequence still starts at 1, and the first new row fails:

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.

Move the sequence past the highest id:

SELECT setval(pg_get_serial_sequence('customers', 'id'),
              (SELECT COALESCE(MAX(id), 0) + 1 FROM customers),
              false);

pg_get_serial_sequence finds the sequence behind both serial and identity columns, and the false argument makes the next value exactly the number given, which also works on an empty table (sequence functions). If you want to keep MySQL's counter exactly, including ids lost to deleted rows, read AUTO_INCREMENT= from SHOW CREATE TABLE customers in MySQL and use that number instead.

Verify the copy

Start with counts and simple aggregates. In MySQL:

SELECT COUNT(*), SUM(id), SUM(balance), SUM(is_active), COUNT(note),
       MIN(created_at), MAX(created_at)
FROM customers;

In PostgreSQL, the same query needs SUM(is_active::int) because the column is now boolean. Zero dates converted to NULL will change MIN(created_at), which is expected.

For a row-by-row check, hash the same columns on both sides in the same order. MySQL:

SET SESSION group_concat_max_len = 1073741824;
SELECT MD5(GROUP_CONCAT(CONCAT_WS('|', id, email, balance, is_active, plan, note)
                        ORDER BY id SEPARATOR '\n'))
FROM customers;

PostgreSQL:

SELECT md5(string_agg(concat_ws('|', id, email, balance, is_active::int, plan, note),
                      E'\n' ORDER BY id))
FROM customers;

On our sample table, both returned the same hash. The group_concat_max_len line matters, because the default limit of 1,024 bytes silently truncates the result. Both CONCAT_WS functions skip NULL values, which is why COUNT(note) belongs in the aggregate check. Leave out columns that each engine prints differently, such as JSON, floats and timestamps with fractions of a second, or format them the same way first. On a large table, hash ranges such as WHERE id BETWEEN 1 AND 100000 so a mismatch points to a small slice.

Finally, run ANALYZE customers; so the planner has statistics for the new table. Our guide to reading PostgreSQL EXPLAIN plans shows what goes wrong without them.

Which method should you pick?

For one or two tables, the file export and \copy give you the most control with no extra software. For a whole database, pgloader saves a lot of DDL writing, as long as it can log in to your MySQL server. A GUI client is the quickest way to move a table now and then, if you check the column types it creates.

FAQ

Can I copy a MySQL table to PostgreSQL without pgloader?

Yes. Create the table in PostgreSQL, export the rows with SELECT ... INTO OUTFILE or mysql --batch, and load the file with psql's \copy. MySQL's default INTO OUTFILE format matches PostgreSQL's default text format for COPY.

How do I convert TINYINT(1) to boolean?

Declare the PostgreSQL column as boolean and load the 0 and 1 values as they are. PostgreSQL accepts 0 and 1 as boolean input, so no conversion is needed during the load.

What happens to '0000-00-00' dates?

PostgreSQL rejects them. Turn them into NULL during the export, for example with IF(col < '1000-01-01', NULL, col) in MySQL, or let pgloader's zero-dates-to-null rule do it.

Why do I get duplicate key errors after importing?

The sequence behind the id column still starts at 1 because the import supplied explicit ids. Run setval with pg_get_serial_sequence to move it past the highest id.

Does pgloader work with MySQL 8?

Only with a workaround. pgloader 3.6 cannot authenticate with caching_sha2_password, the default plugin for MySQL 8 accounts. Use an account with mysql_native_password where your server still allows it, or try the v4 development build.