# TuskShift: MySQL to PostgreSQL migration rules for Laravel (full text)

Source: https://tuskshift.deed.page/ (generated from the same data as the live tool pages and the tuskshift-pg-migrate MCP server).
Summary file: https://tuskshift.deed.page/llms.txt
Every rule and fix below was tested on MariaDB 11.8 and PostgreSQL 17 (Laravel 13 where noted). The tools are pattern-based: treat the output as a checklist and test against a copy of your data.

MCP server for AI coding agents (Node 18+, no dependencies, no telemetry):
  claude mcp add tuskshift-pg-migrate -- npx -y --package=https://tuskshift.deed.page/tuskshift-pg-migrate-1.0.1.tgz tuskshift-pg-migrate
Details: https://tuskshift.deed.page/agents


================================================================
PART 1. Postgres errors Laravel apps hit after moving from MySQL
Page: https://tuskshift.deed.page/errors
================================================================

---- 23505 duplicate key value violates unique constraint "users_pkey"
Link: https://tuskshift.deed.page/errors#seq
SQLSTATE: 23505

Cause:
You copied rows from MySQL with their ids, but the Postgres sequence behind each id column still starts at 1. The first new insert reuses id 1, 2, 3... If your ids have gaps, it fails some inserts and not others, so it looks random.

Fix:
-- once per table that has a serial/identity id:
SELECT setval(pg_get_serial_sequence('users','id'),
              COALESCE((SELECT MAX(id) FROM users), 0) + 1, false);

Notes: Tested on Postgres 17.11: after the setval, the next insert gets MAX(id)+1. Running it again is safe. If the key column is named differently, change 'id'.

---- 42803 column "users.name" must appear in the GROUP BY clause or be used in an aggregate function
Link: https://tuskshift.deed.page/errors#grp
SQLSTATE: 42803

Cause:
MySQL (with ONLY_FULL_GROUP_BY off) lets you select columns that are not grouped and returns a value from an arbitrary row. Postgres refuses. Grouping by the table's primary key is allowed, so groupBy('users.id') with users.* works.

Fix:
-- pick one:
SELECT team_id, MAX(name) FROM users GROUP BY team_id;          -- aggregate it
SELECT DISTINCT ON (team_id) team_id, name FROM users
  ORDER BY team_id, id DESC;                                    -- latest row per group

Notes: Worth knowing: the old MySQL query may have been returning the wrong row the whole time. In our test, select * ... group by user_id on non-strict MariaDB 11.8 returned the oldest row, not the latest.

---- 22003 value "3000000000" is out of range for type integer
Link: https://tuskshift.deed.page/errors#rng
SQLSTATE: 22003

Cause:
Postgres has no unsigned integers. Laravel's unsignedInteger() and increments() become a plain integer on pgsql, so the ceiling drops from 4,294,967,295 to 2,147,483,647. Values above that loaded fine on MySQL and fail on Postgres.

Fix:
ALTER TABLE events ALTER COLUMN hits TYPE bigint;
-- in Laravel migrations: unsignedBigInteger() / bigIncrements() instead of
-- unsignedInteger() / increments()

Notes: Tested with Laravel 13 on MariaDB 11.8 + Postgres 17: inserting 3000000000 works on MariaDB and fails on Postgres with SQLSTATE[22003]; after ALTER ... TYPE bigint it works.

---- 42883 operator does not exist: boolean = integer
Link: https://tuskshift.deed.page/errors#bool
SQLSTATE: 42883

Cause:
tinyint(1) columns usually become boolean on Postgres. MySQL compares them to 1/0 freely; Postgres does not when the 1 is written into the SQL text (whereRaw, DB::select with a literal, raw joins).

Fix:
-- raw SQL:
WHERE active = true
-- or let Laravel bind it (this works on pgsql):
->where('active', 1)

Notes: Tested with pdo_pgsql: a bound 1 is accepted for a boolean column; only a literal 1 in the SQL fails.

---- 42883 operator does not exist: character varying = integer
Link: https://tuskshift.deed.page/errors#vc
SQLSTATE: 42883

Cause:
A string column (phone, code, zip, external_id) is compared with a bare number in raw SQL. MySQL casts silently; Postgres won't guess.

Fix:
WHERE phone = '5551234'          -- quote it
WHERE phone = ?                  -- or bind it

Notes: Tested with pdo_pgsql: a bound value works; only the literal number fails.

---- 22008 date/time field value out of range: "0000-00-00 00:00:00"
Link: https://tuskshift.deed.page/errors#zero
SQLSTATE: 22008

Cause:
MySQL stores zero dates ('0000-00-00') when strict mode is off. Postgres has no such date, so the copy or the insert fails.

Fix:
-- on MySQL, before copying (column must allow NULL):
UPDATE orders SET shipped_at = NULL WHERE shipped_at = '0000-00-00 00:00:00';
ALTER TABLE orders MODIFY due_on date NULL DEFAULT NULL;
UPDATE orders SET due_on = NULL WHERE due_on = '0000-00-00';

Notes: Tested on MariaDB 11.8. Also remove DEFAULT '0000-00-00' from the schema, or new rows bring the problem back.

---- 42703 column "a@x.com" does not exist
Link: https://tuskshift.deed.page/errors#dq
SQLSTATE: 42703

Cause:
If the 'column' in the error is actually a value (an email, a status word), your raw SQL uses double quotes for a string. MySQL treats "text" as a string; Postgres treats it as a column name.

Fix:
WHERE email = 'a@x.com'          -- single quotes for strings
WHERE email = ?                  -- or bind it

Notes: If the name really is a column, check its case: Postgres folds unquoted names to lower case, so a quoted "userId" and userid are different.

---- 42601 syntax error at or near "`"
Link: https://tuskshift.deed.page/errors#bt
SQLSTATE: 42601

Cause:
Backticks are MySQL-only identifier quotes. Laravel's query builder quotes for you, so these come from raw SQL: DB::statement, whereRaw, selectRaw, old migrations.

Fix:
SELECT * FROM users              -- no quotes
SELECT * FROM "users"            -- Postgres identifier quotes

Notes: Tested on Postgres 17. Backticks next to a comma or keyword can also show up as: syntax error at or near "," or near "from". Search the codebase for ` in .php files.

---- 42883 function ifnull(text, unknown) does not exist
Link: https://tuskshift.deed.page/errors#fn
SQLSTATE: 42883

Cause:
A MySQL-only function in raw SQL. Common ones and their Postgres versions are below.

Fix:
IFNULL(a, b)              -> COALESCE(a, b)
GROUP_CONCAT(email)       -> string_agg(email, ',' ORDER BY id)
DATE_FORMAT(d, '%Y')      -> to_char(d, 'YYYY')
IF(cond, a, b)            -> CASE WHEN cond THEN a ELSE b END
UNIX_TIMESTAMP(d)         -> extract(epoch FROM d)
CURDATE()                 -> current_date

Notes: COALESCE, string_agg and to_char tested on Postgres 17.11.

---- 22021 invalid byte sequence for encoding "UTF8": 0x00
Link: https://tuskshift.deed.page/errors#nul
SQLSTATE: 22021

Cause:
Some MySQL text values contain NUL bytes (often from binary data or bad imports). Postgres text can't store them, so the load fails.

Fix:
-- on MySQL, find and strip them before copying:
SELECT id FROM t WHERE INSTR(name, CHAR(0)) > 0;
UPDATE t SET name = REPLACE(name, CHAR(0), '') WHERE INSTR(name, CHAR(0)) > 0;

Notes: Silent variant: through PHP's pdo_pgsql (PHP 8.4, Postgres 17), a bound value 'a\0b' was stored as 'a' with no error. Everything after the NUL is lost. Check before you copy. Tested on MariaDB 11.8 and Postgres 17.

---- 25P02 current transaction is aborted, commands ignored until end of transaction block
Link: https://tuskshift.deed.page/errors#abort
SQLSTATE: 25P02

Cause:
This is a symptom. An earlier statement in the same transaction failed; MySQL would carry on, but Postgres refuses every statement until rollback. Code that catches an exception and keeps using the same transaction hits this.

Fix:
-- find the FIRST error in the log, fix that one.
-- if you must try-and-continue inside a transaction, wrap the risky step
-- in a savepoint (nested DB::transaction() does this in Laravel).

Notes: Tested on Postgres 17: after a failing statement, the next one in the block returns 25P02.

---- (no error) search or login finds nothing: WHERE email LIKE ... returns 0 rows
Link: https://tuskshift.deed.page/errors#ci
SQLSTATE: none (silent behavior change)

Cause:
Not an error, which makes it worse. MySQL's _ci collations make = and LIKE case-insensitive. Postgres is case-sensitive, so Name@Example.com no longer matches name@example.com.

Fix:
WHERE email ILIKE 'a@%'
WHERE lower(email) = lower(?)
-- or the citext extension for email columns

Notes: Tested on Postgres 17.11. Also check unique indexes: two rows differing only in case were blocked on MySQL and are allowed on Postgres.


================================================================
PART 2. Schema risk check: what breaks on Postgres
Page: https://tuskshift.deed.page/
================================================================

# Laravel MySQL → PostgreSQL schema risk check

Paste SHOW CREATE TABLE output, a mysqldump --no-data file, or Laravel migration files. You get the things that break on Postgres, most dangerous first. Runs in your browser; nothing is uploaded.

Get the schema: mysqldump --no-data your_db > schema.sql or SHOW CREATE TABLE users\G

Already moved and seeing errors? Paste your Postgres error, get the fix. Raw SQL to convert? MySQL → Postgres SQL translator.

## What it checks

- int / smallint unsigned: Postgres has no unsigned types; Laravel makes them integer / smallint, so values above 2,147,483,647 (or 32,767) fail with "out of range".

- bigint unsigned above 2^63-1.

- AUTO_INCREMENT: sequences stay at 1 after a bulk copy, and the first insert fails with "duplicate key value violates unique constraint".

- TIMESTAMP columns: a default dump writes UTC, so on a non-UTC server values shift by hours. Row counts still match.

- _ci collations on email/username/slug: comparisons and unique indexes become case-sensitive.

- tinyint(1) → boolean: raw WHERE col = 1 fails with "operator does not exist: boolean = integer".

- ENUM, SET, YEAR, ZEROFILL, JSON stored as longtext (MariaDB), binary → bytea, spatial types.

- Zero dates (DEFAULT '0000-00-00'), ON UPDATE CURRENT_TIMESTAMP, VIRTUAL generated columns (Postgres 17 and older: STORED only).

- Prefix indexes like KEY (bio(50)), FULLTEXT, MyISAM tables (orphan rows break foreign keys).

- Laravel migrations: increments(), unsignedInteger(), timestamps(), enum(), set(), useCurrentOnUpdate(), MySQL collations, and backticks in raw SQL.

Schema only. Data problems (zero dates in rows, NUL bytes, tinyint(1) = 2, values already over 2^31, case-duplicate emails) need a look at the data: use the free read-only PHP checker (one file, MIT) against your MySQL database.

Each rule was tested on MariaDB 11.8 and Postgres 17 with Laravel 13. The tool is pattern-based, so treat it as a checklist, not proof.


================================================================
PART 3. MySQL to Postgres SQL translator rules
Page: https://tuskshift.deed.page/translate
================================================================

# MySQL → PostgreSQL SQL translator

Paste MySQL queries or Laravel DB::raw / whereRaw / selectRaw fragments. You get Postgres SQL and a short reason for every change. Runs in your browser; nothing is uploaded.

Moving a whole Laravel app?
Raw SQL is one part. The Migration Kit ($49, $34.30 with LAUNCH30 until Nov 5) covers the schema, data fixes, rehearsal, cutover and rollback. Already on Postgres and seeing errors: the Error Fix Pack ($19) has tested fixes for the sequence, boolean = integer, out-of-range, zero-date, TIMESTAMP-shift and GROUP BY errors.

## Examples (each one tested)

Every pair below ran on MariaDB 11.8 (MySQL side) and Postgres 17.11 against the same data, and both returned the same rows. For the ON CONFLICT ones the conflict column was filled in as the tool tells you to.

### IFNULL, backticks, double-quoted strings, LIMIT offset, count

MySQL

```
SELECT `id`, IFNULL(`name`, "n/a") AS name FROM `users` WHERE `active` = 1 ORDER BY `id` LIMIT 1, 2
```

Postgres

```
SELECT "id", COALESCE("name", 'n/a') AS name FROM "users" WHERE "active" = true ORDER BY "id" LIMIT 2 OFFSET 1
```

### GROUP_CONCAT with DISTINCT, ORDER BY and SEPARATOR → string_agg

MySQL

```
SELECT user_id, GROUP_CONCAT(DISTINCT status ORDER BY status SEPARATOR ', ') AS statuses FROM orders GROUP BY user_id ORDER BY user_id
```

Postgres

```
SELECT user_id, string_agg(DISTINCT status::text, ', ' ORDER BY status::text) AS statuses FROM orders GROUP BY user_id ORDER BY user_id
```

### DATE_FORMAT → to_char

MySQL

```
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s'), DATE_FORMAT(created_at, '%b %e, %Y'), DATE_FORMAT(created_at, '%d/%m/%y %h:%i %p'), DATE_FORMAT(created_at, '%W %M week of %Y') FROM orders ORDER BY id
```

Postgres

```
SELECT to_char(created_at, 'YYYY-MM-DD HH24:MI:SS'), to_char(created_at, 'Mon FMDD, YYYY'), to_char(created_at, 'DD/MM/YY HH12:MI AM'), to_char(created_at, 'FMDay FMMonth" week of "YYYY') FROM orders ORDER BY id
```

### IF() → CASE WHEN, tinyint(1) = 1 → boolean

MySQL

```
SELECT id, IF(total > 50, 'big', 'small') AS size, IF(paid = 1, "yes", "no") AS p FROM orders ORDER BY id
```

Postgres

```
SELECT id, CASE WHEN total > 50 THEN 'big' ELSE 'small' END AS size, CASE WHEN paid = true THEN 'yes' ELSE 'no' END AS p FROM orders ORDER BY id
```

### LIKE on a _ci column → ILIKE

MySQL

```
SELECT COUNT(*) FROM users WHERE name LIKE '%ALI%' OR email LIKE "BOB%"
```

Postgres

```
SELECT COUNT(*) FROM users WHERE name ILIKE '%ALI%' OR email ILIKE 'BOB%'
```

### UNIX_TIMESTAMP / FROM_UNIXTIME

MySQL

```
SELECT UNIX_TIMESTAMP('2026-01-01 00:00:00'), FROM_UNIXTIME(1767225600, '%Y-%m-%d %H:%i'), UNIX_TIMESTAMP(created_at) FROM users ORDER BY id
```

Postgres

```
SELECT extract(epoch FROM '2026-01-01 00:00:00'::timestamptz)::bigint, to_char(to_timestamp(1767225600), 'YYYY-MM-DD HH24:MI'), extract(epoch FROM created_at)::bigint FROM users ORDER BY id
```

### DATEDIFF, DATE_ADD, INTERVAL 1 DAY

MySQL

```
SELECT id, DATEDIFF(created_at, '2026-01-01'), DATE(DATE_ADD(created_at, INTERVAL 7 DAY)), DATE_SUB(created_at, INTERVAL 2 HOUR) FROM users WHERE created_at > '2026-01-01' - INTERVAL 1 DAY ORDER BY id
```

Postgres

```
SELECT id, (created_at::date - '2026-01-01'::date), DATE((created_at + interval '7 day')), (created_at - interval '2 hour') FROM users WHERE created_at > '2026-01-01'::timestamp - interval '1 day' ORDER BY id
```

### ORDER BY FIELD()

MySQL

```
SELECT id FROM users ORDER BY FIELD(id, 3, 1, 2), id
```

Postgres

```
SELECT id FROM users ORDER BY COALESCE(array_position(ARRAY[3, 1, 2], id), 0), id
```

### FIND_IN_SET in WHERE

MySQL

```
SELECT id FROM users WHERE FIND_IN_SET('php', tags) ORDER BY id
```

Postgres

```
SELECT id FROM users WHERE (COALESCE(array_position(string_to_array(tags, ','), ('php')::text), 0) > 0) ORDER BY id
```

### INSERT IGNORE → ON CONFLICT DO NOTHING

MySQL

```
INSERT IGNORE INTO users (id, name, email) VALUES (1, 'dup', 'dup@x.io'), (5, 'Dan', 'dan@x.io')
```

Postgres

```
INSERT INTO users (id, name, email) VALUES (1, 'dup', 'dup@x.io'), (5, 'Dan', 'dan@x.io') ON CONFLICT DO NOTHING
```

### ON DUPLICATE KEY UPDATE … VALUES() → ON CONFLICT … DO UPDATE … EXCLUDED

MySQL

```
INSERT INTO stats (day, hits) VALUES ('2026-01-01', 5), ('2026-01-03', 1) ON DUPLICATE KEY UPDATE hits = hits + VALUES(hits)
```

Postgres

```
INSERT INTO stats (day, hits) VALUES ('2026-01-01', 5), ('2026-01-03', 1) ON CONFLICT (day) DO UPDATE SET hits = stats.hits + EXCLUDED.hits
```

### REPLACE INTO

MySQL

```
REPLACE INTO stats (day, hits) VALUES ('2026-01-02', 9), ('2026-01-04', 2)
```

Postgres

```
INSERT INTO stats (day, hits) VALUES ('2026-01-02', 9), ('2026-01-04', 2) ON CONFLICT (day) DO UPDATE SET day = EXCLUDED.day, hits = EXCLUDED.hits
```

### JSON_UNQUOTE(JSON_EXTRACT()) → #>>

MySQL

```
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(meta, '$.plan')) AS plan FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(meta, '$.plan')) = 'pro' ORDER BY id
```

Postgres

```
SELECT id, (meta #>> '{plan}') AS plan FROM users WHERE (meta #>> '{plan}') = 'pro' ORDER BY id
```

### TIMESTAMPDIFF

MySQL

```
SELECT id, TIMESTAMPDIFF(HOUR, '2026-01-01 00:00:00', created_at), TIMESTAMPDIFF(MONTH, '2026-01-01 00:00:00', created_at), TIMESTAMPDIFF(DAY, created_at, '2026-04-01 00:00:00') FROM users ORDER BY id
```

Postgres

```
SELECT id, trunc(extract(epoch FROM (created_at - '2026-01-01 00:00:00')) / 3600)::bigint, (extract(year FROM age(created_at, '2026-01-01 00:00:00')) * 12 + extract(month FROM age(created_at, '2026-01-01 00:00:00')))::int, trunc(extract(epoch FROM ('2026-04-01 00:00:00' - created_at)) / 86400)::bigint FROM users ORDER BY id
```

## What it converts

Backticks → double quotes · "strings" → 'strings' · backslash escapes · IFNULL → COALESCE · IF() → CASE · GROUP_CONCAT → string_agg · DATE_FORMAT / STR_TO_DATE / FROM_UNIXTIME formats → to_char / to_date patterns · NOW(), CURDATE(), CURTIME(), SYSDATE(), UTC_TIMESTAMP() · UNIX_TIMESTAMP · DATEDIFF, DATE_ADD/DATE_SUB, INTERVAL 30 DAY, TIMESTAMPDIFF, YEAR()/MONTH()/DAY() · LIMIT offset, count · INSERT IGNORE · ON DUPLICATE KEY UPDATE + VALUES() · REPLACE INTO (with warning) · col = 1 on boolean-looking columns · LIKE → ILIKE · REGEXP → ~* · || and && as OR/AND · <=> · FIELD() · FIND_IN_SET · SUBSTRING_INDEX · LOCATE / INSTR · JSON_EXTRACT and '$.a.b' paths · CAST AS UNSIGNED/SIGNED/CHAR/DATETIME · RAND() · index hints, SQL_CALC_FOUND_ROWS · # comments. It warns about CONCAT/GREATEST NULL behaviour, LENGTH bytes vs characters, GROUP BY strictness, @variables, UPDATE/DELETE … LIMIT and zero dates.

It is a rule-based rewriter, not a full SQL parser. Read the output and run it against a copy of your data before you ship it.

## FAQ

### How do I replace GROUP_CONCAT in Postgres?

Use string_agg(col::text, ',' ORDER BY col). GROUP_CONCAT(DISTINCT x ORDER BY x SEPARATOR '; ') becomes string_agg(DISTINCT x::text, '; ' ORDER BY x::text): with DISTINCT the ORDER BY must be the same expression. Postgres has no group_concat_max_len limit.

### What is IFNULL in Postgres?

COALESCE(a, b). It works on MySQL too, so you can switch before the migration.

### How do I convert DATE_FORMAT to Postgres?

to_char(value, pattern) with different letters: %Y → YYYY, %m → MM, %d → DD, %H → HH24, %i → MI, %s → SS, %b → Mon, %M → FMMonth, %e → FMDD, %p → AM. Literal words inside the pattern must be double-quoted.

### Does LIMIT 10, 20 work on Postgres?

No. LIMIT offset, count is MySQL-only. Write LIMIT 20 OFFSET 10. If the values are bound (LIMIT ?, ?), swap the bindings too.

### What replaces INSERT IGNORE and ON DUPLICATE KEY UPDATE?

INSERT ... ON CONFLICT DO NOTHING, and INSERT ... ON CONFLICT (unique_column) DO UPDATE SET col = EXCLUDED.col. Postgres needs the conflict column(s) named and a unique index on them. A bare column on the right-hand side (hits = hits + 1) is ambiguous on Postgres; write table.hits. In Laravel, upsert() generates this for you.

### Why does my search return fewer rows after moving to Postgres?

MySQL _ci collations make LIKE and = case-insensitive. Postgres is case-sensitive. Use ILIKE, or lower(col) = lower(?) with an index on lower(col).

### Why does WHERE active = 1 fail on Postgres?

tinyint(1) columns usually become boolean, and boolean = integer is error 42883. Write active = true in raw SQL. A bound value from Laravel ->where('active', 1) is accepted.

### Is my SQL uploaded?

No. The translator is plain JavaScript that runs in your browser. Nothing is sent anywhere.

Not moved yet? Check the schema first with the free schema risk check (paste SHOW CREATE TABLE) and the free read-only data checker. Already moved and seeing an error: paste the error, get the fix.


================================================================
PART 4. MySQL to Postgres schema (DDL) converter
Page: https://tuskshift.deed.page/ddl
================================================================

# MySQL → PostgreSQL schema converter

Paste SHOW CREATE TABLE output, mysqldump --no-data or a phpMyAdmin structure export. You get PostgreSQL DDL in run order (tables, indexes, triggers, foreign keys, sequence resets) and a note for every change that can bite. Runs in your browser; nothing is uploaded.

tinyint(1) → boolean

TIMESTAMP → timestamptz (default: timestamp, like Laravel)

The schema is the easy part.
What breaks a Laravel move is the data and the cutover: shifted TIMESTAMPs, case-sensitive logins, sequences, raw SQL. The Migration Kit ($49, $34.30 with LAUNCH30 until Nov 5) covers schema, data checks, rehearsal, cutover and rollback. Already on Postgres and hitting errors: the Error Fix Pack ($19).

## Examples (each one tested)

Each MySQL schema below was loaded into MariaDB 11.8, dumped, converted and run on PostgreSQL 17.11; the rows were copied across and inserts, updates, the CHECK, the foreign key, the trigger and the full-text index were exercised.

### Laravel users table (SHOW CREATE TABLE / mysqldump)

MySQL

```
CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) NOT NULL,
`is_admin` tinyint(1) NOT NULL DEFAULT 0,
`remember_token` varchar(100) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1042 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

PostgreSQL

```
CREATE TABLE users (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
email_verified_at timestamp(0),
password varchar(255) NOT NULL,
is_admin boolean NOT NULL DEFAULT false,
remember_token varchar(100),
created_at timestamp(0),
updated_at timestamp(0),
PRIMARY KEY (id)
);

-- Indexes
CREATE UNIQUE INDEX users_email_unique ON users (email);

-- After loading data: move each identity sequence past the copied ids, or the first insert fails with "duplicate key value violates unique constraint"
SELECT setval(pg_get_serial_sequence('users', 'id'), GREATEST(COALESCE(MAX(id), 0) + 1, 1042), false) FROM users;
```

### ENUM, SET, unsigned ints, zero-date default, ON UPDATE CURRENT_TIMESTAMP, prefix + FULLTEXT index, foreign key

MySQL

```
CREATE TABLE `orders` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`status` enum('pending','paid','refunded') NOT NULL DEFAULT 'pending',
`flags` set('gift','rush') DEFAULT NULL,
`qty` smallint(5) unsigned NOT NULL DEFAULT 1,
`code` char(8) DEFAULT NULL,
`note` mediumtext DEFAULT NULL,
`meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
`shipped_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` datetime(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3),
PRIMARY KEY (`id`),
KEY `idx_code` (`code`(4)),
FULLTEXT KEY `ft_note` (`note`),
CONSTRAINT `orders_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

PostgreSQL

```
CREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
user_id bigint NOT NULL,
status varchar(8) NOT NULL DEFAULT 'pending',
flags text,
qty integer NOT NULL DEFAULT 1,
code char(8),
note text,
meta jsonb,
shipped_at timestamp(0),
updated_at timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CHECK (status IN ('pending', 'paid', 'refunded'))
);

-- Indexes
CREATE INDEX idx_code ON orders (code);
CREATE INDEX orders_note_fulltext ON orders USING gin (to_tsvector('english', coalesce(note, '')));

-- ON UPDATE CURRENT_TIMESTAMP replacement
CREATE OR REPLACE FUNCTION tuskshift_touch() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
NEW := jsonb_populate_record(NEW, jsonb_build_object(TG_ARGV[0], CURRENT_TIMESTAMP));
RETURN NEW;
END $$;
CREATE TRIGGER orders_updated_at_touch BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION tuskshift_touch('updated_at');

-- Foreign keys (add after the data copy; MyISAM or FOREIGN_KEY_CHECKS=0 imports can contain orphans)
ALTER TABLE orders ADD CONSTRAINT orders_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

-- After loading data: move each identity sequence past the copied ids, or the first insert fails with "duplicate key value violates unique constraint"
SELECT setval(pg_get_serial_sequence('orders', 'id'), COALESCE(MAX(id), 0) + 1, false) FROM orders;
```

### phpMyAdmin export (keys and AUTO_INCREMENT in separate ALTER TABLE statements)

MySQL

```
CREATE TABLE `posts` (
`id` int(10) UNSIGNED NOT NULL,
`slug` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`published` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `posts`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `posts_slug_unique` (`slug`);

ALTER TABLE `posts`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
```

PostgreSQL

```
CREATE TABLE posts (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
slug varchar(191) NOT NULL,
published boolean NOT NULL DEFAULT true,
PRIMARY KEY (id)
);

-- Indexes
CREATE UNIQUE INDEX posts_slug_unique ON posts (slug);

-- After loading data: move each identity sequence past the copied ids, or the first insert fails with "duplicate key value violates unique constraint"
SELECT setval(pg_get_serial_sequence('posts', 'id'), GREATEST(COALESCE(MAX(id), 0) + 1, 3), false) FROM posts;
```

## What it converts

AUTO_INCREMENT → GENERATED BY DEFAULT AS IDENTITY + setval() after load (respects the table's AUTO_INCREMENT=) · tinyint(1)/bool/bit(1) → boolean · tinyint → smallint · smallint unsigned → integer · int unsigned → bigint · mediumint → integer · bigint unsigned → bigint (warned) · decimal → numeric · float/double → real/double precision · datetime(n)/timestamp(n) → timestamp(n) · year → smallint · enum → varchar + CHECK · set → text (warned) · json and MariaDB longtext + json_valid → jsonb · blob/binary → bytea · zero-date defaults dropped · current_timestamp() defaults · ON UPDATE CURRENT_TIMESTAMP → trigger · COMMENT → COMMENT ON · KEY/UNIQUE KEY → CREATE INDEX (duplicate names renamed) · prefix indexes → full column (warned) · FULLTEXT → GIN to_tsvector · SPATIAL → GiST (PostGIS) · FOREIGN KEY → ALTER TABLE ADD CONSTRAINT after the data · generated columns → STORED · reserved names (order, group, user) quoted · ENGINE/CHARSET/COLLATE removed, with case-sensitivity warnings on _ci email/username/slug columns · phpMyAdmin ALTER TABLE ADD/MODIFY sections folded in.

It converts structure only. Move the data with pgloader or CSV + COPY, then run the setval lines. It is a rule-based converter, not a full SQL parser: read the output and run it on a scratch database first.

## FAQ

### How do I convert a MySQL schema to PostgreSQL?

Dump the schema only (mysqldump --no-data mydb, or SHOW CREATE TABLE per table, or a phpMyAdmin structure export), paste it here and copy the result. Run the CREATE TABLE part, load the data, then run the index, foreign key and setval sections. Read the notes first: some changes (TIMESTAMP shifts, case-sensitivity, unsigned overflow) are not visible in the DDL.

### What is AUTO_INCREMENT in PostgreSQL?

An identity column: id bigint GENERATED BY DEFAULT AS IDENTITY (or bigserial, which Laravel uses). BY DEFAULT lets you insert the old ids during the copy. After the copy you must run setval() to move the sequence past MAX(id), otherwise the first insert fails with "duplicate key value violates unique constraint".

### What does tinyint(1) become in PostgreSQL?

boolean, which is what Laravel boolean() creates on pgsql. Raw SQL like WHERE active = 1 then fails with "operator does not exist: boolean = integer"; write = true. If a tinyint(1) column really stores 0-3, keep it as smallint (untick the option).

### What is int unsigned in PostgreSQL?

Postgres has no unsigned types. int unsigned goes up to 4,294,967,295, Postgres integer only to 2,147,483,647, so the converter uses bigint. bigint unsigned stays bigint with a warning: use numeric(20,0) if values can exceed 9,223,372,036,854,775,807.

### How do I convert ENUM to PostgreSQL?

The converter makes a varchar with a CHECK (col IN (...)) constraint, the same thing Laravel enum() does on pgsql. A native CREATE TYPE ... AS ENUM also works but is harder to change later. Note that ORDER BY becomes alphabetical.

### What replaces ON UPDATE CURRENT_TIMESTAMP?

A BEFORE UPDATE trigger. The converter emits one small generic trigger function and one trigger per column.

### Why was my index renamed?

MySQL index names are per table; Postgres index names are unique per schema. Two tables with an index called idx_user would make the second CREATE INDEX fail with "relation already exists", so the converter prefixes the table name.

### Is my schema uploaded?

No. The converter is plain JavaScript that runs in your browser. Nothing is sent anywhere.

Next steps: run the free schema risk check for the data-level traps, the free read-only data checker against your MySQL database, and the SQL translator for raw queries. Already moved and seeing an error: paste the error, get the fix.


================================================================
PART 5. MCP server, CLI and skill for AI coding agents
Page: https://tuskshift.deed.page/agents
================================================================

# Postgres migration tools for AI coding agents

An MCP server and a skill file. With them, Claude Code, Cursor, Codex or any MCP client can audit a Laravel MySQL app and port it to PostgreSQL using rules tested on real MariaDB 11.8 and Postgres 17 servers. Models guess at this. These tools carry the tested answers. It runs on your machine and has no dependencies. Your schema and code are never uploaded.

## Install (free)

Claude Code

```
claude mcp add tuskshift-pg-migrate -- npx -y --package=https://tuskshift.deed.page/tuskshift-pg-migrate-1.0.1.tgz tuskshift-pg-migrate
```

Cursor (~/.cursor/mcp.json), Windsurf, Claude Desktop config

```
{
"mcpServers": {
"tuskshift-pg-migrate": {
"command": "npx",
"args": [
"-y",
"--package=https://tuskshift.deed.page/tuskshift-pg-migrate-1.0.1.tgz",
"tuskshift-pg-migrate"
]
}
}
}
```

Codex (~/.codex/config.toml)

```
[mcp_servers.tuskshift-pg-migrate]
command = "npx"
args = ["-y", "--package=https://tuskshift.deed.page/tuskshift-pg-migrate-1.0.1.tgz", "tuskshift-pg-migrate"]
```

Claude Desktop, one click: tuskshift-pg-migrate-1.0.1.mcpb. Skill for Claude Code, which gives the agent the audit → scan → convert → test → fix workflow: tuskshift-pg-migrate-skill.zip. Unzip it into ~/.claude/skills/. Raw package: tuskshift-pg-migrate-1.0.1.tgz, which also works as a CLI (tuskshift-pg-migrate scan ./app). Needs Node 18 or later.

## Tools

| Tool | What the agent gets | Tier

| check_mysql_schema | Risks in a mysqldump, SHOW CREATE TABLE or Laravel migration: unsigned ranges, tinyint(1)→boolean, ENUM/SET, TIMESTAMP hour shifts, zero dates, _ci collations becoming case-sensitive, FULLTEXT, MyISAM, sequence resets | Free

| lookup_postgres_error | Cause and tested fix for the errors Laravel apps hit after the switch: 23505 duplicate key (sequence), 42803 GROUP BY, 22003 out of range, 42883 boolean = integer, zero dates, backticks and more | Free

| translate_mysql_query | MySQL SQL or a whereRaw fragment translated to Postgres, with the reason for every change | Free

| convert_mysql_schema | Runnable Postgres DDL: identity columns, indexes, FKs added after the data load, comments, setval lines, and a note on each lossy change | Pro

| scan_laravel_project | Every raw SQL call in the repo with file:line and its Postgres version, plus migration and config blockers (read-only) | Pro

Free tier: the three free tools in full, translation up to 5 statements per call, a 2-table preview of the converter and the scan's totals plus its first 3 findings.

## Tested, not guessed

Before each release, the test run sends real queries through the MCP tools and runs them on MariaDB 11.8 and Postgres 17. The results must match. In the 1.0.1 run (Oct 27, 2026):

- 24 of 24 translated queries returned identical rows on both servers. They include GROUP_CONCAT, DATE_FORMAT, TIMESTAMPDIFF, ON DUPLICATE KEY UPDATE, INSERT IGNORE, REPLACE INTO and JSON_EXTRACT.

- 6 of 6 raw SQL calls found by the project scan in a sample Laravel app returned identical rows after translation. All 13 expected findings were reported, and nothing under vendor/ was flagged.

- 3 of 3 schema dumps (mysqldump, SHOW CREATE TABLE, phpMyAdmin) loaded on MariaDB, converted and ran cleanly on Postgres, with matching table counts.

- 19 of 19 protocol and free-tier checks passed, along with a run through the official MCP TypeScript SDK client.

## Pro: $39 one-time

Pro unlocks the full schema conversion, the full project scan with every file:line and translation, and batch translation. The license key comes from Gumroad. Pass it to activate_license or set TUSKSHIFT_LICENSE_KEY. Buy Pro on Gumroad for $39. It's a one-time payment, and the key arrives by email. Without a key, every tool runs in free mode. For the human side of the move (rehearsal, data fixes, cutover and rollback runbooks), see the $49 Migration Kit.

## Privacy and checksums

The package has no telemetry and no dependencies. The only network request is to api.gumroad.com/v2/licenses/verify. It runs when you activate a Pro key and at most weekly after that, and it sends only the key.

```
fd2771675282723313978fa136e203accad9c78f6f3908dfcc81f5e8bb4f310f  tuskshift-pg-migrate-1.0.1.mcpb
efaea38a14f1e880c024302584df17e7b1ef9f14163d18ec3e8878914e552228  tuskshift-pg-migrate-1.0.1.tgz
f615107866d9ab9cb85ce98fe0614e7e9d86386a9e568b2cfe7f641000f556e7  tuskshift-pg-migrate-skill.zip
```