Type mapping

What your MySQL TINYINT(1) / ENUM / DECIMAL / JSON / temporal types become on Postgres (and vice versa), and on SQLite / D1 — the cross-engine translation policies.

sluice never translates one dialect straight to another. Every column type maps source-dialect → typed IR → target-dialect: source-specific knowledge lives in readers, target-specific knowledge in writers, and the IR is the only shared contract. That's why the four-direction matrix needs four readers and four writers, not twelve pairwise tables. This page is the operator-facing summary of those policies; the canonical, always-current source is docs/type-mapping.md and the runtime value contract in docs/value-types.md.

Core vs extension types #

The IR type system is a two-tier hierarchy, and the tier decides what happens on an engine that lacks a type:

• Core types — integers, decimal, float, boolean, char/varchar/text, binary/blob, date/time/datetime/timestamp, JSON — are the types every relational engine has in some form. Every engine reads and writes them; they are the lingua franca.
• Extension types — ENUM, SET, UUID, arrays, PostGIS geometry, and the Postgres network types (inet/cidr/macaddr) — are types only some engines support natively. Each engine's own reader/writer type dispatch decides what it handles; an engine that lacks one either applies a documented degradation (e.g. Postgres array → MySQL JSON) or refuses loudly. Postgres extension types (hstore, citext, pgvector, PostGIS) are opt-in via --enable-pg-extension EXT and refuse loudly at schema-read if the flag is absent (SLUICE-E-SCHEMA-EXTENSION-NOT-ENABLED) — never silently dropped.

Adding a new engine never amends the core; it provides the reader/writer code, and its own type dispatch IS the declaration — a missing arm refuses loudly rather than degrading silently. The orchestrator's capability questions are strategy questions (which bulk-load method, which CDC transport, which schema scope). And since v0.119.0 the target engine is asked to dry-run its own column-type emit before any data moves, so an unrenderable column is refused up front — naming the table, column and type — instead of surfacing mid-run from the target's CREATE TABLE.

MySQL ↔ Postgres #

The most-travelled direction. Notable rows below; the full table is in the canonical doc.

Decimal values past the target scale: DECIMAL-SCALE-EXCEEDED (v0.156.4) #

An unconstrained Postgres numeric lands on a MySQL-family target as DECIMAL(65,30), MySQL's widest fixed-point form: 35 integer digits and 30 fractional digits. Past 35 integer digits MySQL refuses the write itself (Error 1264). Past 30 fractional digits it does not: MySQL and MariaDB round the value and report only Note 1265, which even strict sql_mode accepts. The bulk copy (LOAD DATA and the batched INSERT path) was always loud, because it refuses a strict-mode write whose warning list is non-empty; the CDC applier never read that list. So through v0.156.3, sync (log-based or postgres-trigger), chain restore, sync from-backup and the added-column backfill stored such values rounded, at exit 0 — measured on MySQL 8.0.46 and MariaDB 11.4: 0.1234567890123456789012345678901 landed as 0.123456789012345678901234567890, and -0.99999999999999999999999999999999 as -1.000000000000000000000000000000. A DECIMAL column's literal DEFAULT past the scale was stored rounded the same way on CREATE TABLE and on a forwarded ADD COLUMN.

Since v0.156.4 sluice refuses any DECIMAL value, and any DECIMAL column's literal DEFAULT, whose significant fractional digits exceed the target column's scale — before the driver sees it, with SLUICE-E-VALUE-UNREPRESENTABLE marked DECIMAL-SCALE-EXCEEDED, naming the column and the digits lost. It applies to a constrained DECIMAL(p,s) column too, and to a float column retyped to a narrower DECIMAL with --type-override. Trailing zeros past the scale lose nothing and are not refused (1.5000…0 lands as 1.5). To proceed, round the value on the source if the digits are not needed, or --type-override the column to a text type to carry every digit. numeric[] values land in a JSON column as strings and were always exact.

Non-ASCII text in MySQL-family DEFAULTs and expressions #

Row values never pass through the catalog, so they were never affected by what follows. Schema text does: sluice builds the target's DEFAULTs, generated columns, CHECK constraints and functional indexes from the source catalog, and both MySQL's and MariaDB's information_schema render some non-ASCII text unfaithfully. sluice recovers it; where it cannot recover it faithfully, it refuses the schema read, naming the object, rather than guessing.

• A literal DEFAULT beyond the Basic Multilingual Plane (v0.156.1). Both servers store COLUMN_DEFAULT in utf8mb3, so VARCHAR(20) DEFAULT '😀x' reads back as ?x. A character default that reads back with a ? is re-read — through a DEFAULT() probe on MySQL and MariaDB, from SHOW CREATE TABLE on PlanetScale and Vitess — and must agree with the catalog text, or the read refuses; a genuine ? default stays ?. Refused rather than guessed: such a default on a non-UTF-8 column charset on PlanetScale/Vitess, and an ENUM/SET default whose label holds such a character.
• Expressions — an expression DEFAULT (…), a generated column, a CHECK, a functional index (v0.156.2, Bug 288). MySQL (and PlanetScale, Vitess) stores each string literal of an expression in the encoding its charset introducer names and renders every stored byte as a separate character, so DEFAULT ('é') read back as _utf8mb4'é' and, through v0.156.1, é landed on the target as é and 中 as 中 — on migrate, sync cold start, restore and a forwarded ADD COLUMN, to MySQL and Postgres targets alike. sluice now undoes the widening and decodes each literal by its introducer: utf8mb4, utf8mb3 and binary bytes as UTF-8, latin1 bytes as their latin1 characters. It refuses a latin1 byte in 0x80–0x9F (MySQL's latin1 is cp1252 there — 0x80 is €), a literal in any other charset (gbk, …), and a non-ASCII literal with no introducer. The decoded expression must also agree with that object's own SHOW CREATE TABLE line, or the read refuses. MariaDB's information_schema is faithful for the BMP but writes one ? per byte for a character beyond it (an emoji became ????, on every LTS line from 10.11 to 12.3); sluice takes the SHOW CREATE TABLE text the ?s match, searched only within the object's own clause, and refuses on no match or an ambiguous one.

Column names inside expressions, on a Postgres target (v0.160.1) #

MySQL and SQLite resolve a column name inside a stored expression case-insensitively, and MySQL keeps a functional index's expression as typed, so KEY (plant, (lower(status))) over a column named STATUS is valid there. Postgres folds every unquoted identifier to lower case. Through v0.160.0 sluice carried such an expression across with its identifiers bare, so a MySQL functional index, generated column or CHECK, or a SQLite partial-index predicate, that named an upper- or mixed-case column failed on a Postgres target with SQLSTATE 42703 (column "…" does not exist) — even when the case matched exactly. A generated column or CHECK failed at CREATE TABLE; a functional index failed at the index phase, after the whole copy, so a sync start cold start stopped with every row copied and no CDC position (reported as SLUICE-E-INDEX-TARGET-MISSING). Nothing was lost (GitHub #356). Since v0.160.1 the Postgres writer resolves each bare column reference against the table's columns, case-insensitively as the source does, and emits the column's real name, quoted when Postgres would otherwise fold it. LEADING, TRAILING and BOTH inside TRIM(…) need a position rule, because sluice strips the backticks that tell MySQL's column `both` from the keyword both, and either misreading is valid Postgres that trims different characters. The word stays the keyword only as the first token of TRIM's arguments with an operand after it (TRIM(BOTH 'x' FROM s)); before FROM, ) or an operator it is the column (trim(`both` from `code`) trims the characters held in column both). v0.160.1 got this wrong: it kept the three words bare anywhere inside TRIM, so a column used as the remove-string became the keyword and a generated column or CHECK computed different values at exit 0 (Bug 311, fixed in v0.160.2). schema diff translates a CHECK the same way, so the quoted form is not reported as drift. MySQL and SQLite targets were never affected. Recovery: a stream that already failed this way refuses a plain re-run with SLUICE-E-COLDSTART-TARGET-NOT-EMPTY; after upgrading, re-run it with --reset-target-data --yes.

TRIM with a multi-character remove-string: TRIM-REMOVE-STRING-SEMANTICS (v0.160.3) #

MySQL and Postgres both accept TRIM(BOTH 'xy' FROM s) and mean different things by it: MySQL strips 'xy' as a repeated substring, Postgres strips any x or y character as a set. TRIM(BOTH 'xy' FROM 'yxAyx') is yxAyx on MySQL and A on Postgres. Through v0.160.2 a generated column, CHECK, view or unique expression index using such a TRIM crossed between the two verbatim and computed or accepted different values at exit 0 (HIGH, silent, Bug 312; the basic MySQL → Postgres case affects every earlier release, and v0.160.1 and v0.160.2 each made more shapes silent). Since v0.160.3 sluice refuses it, in either direction, with TRIM-REMOVE-STRING-SEMANTICS naming the object. The two engines agree only when the remove-string is a text literal of at most one byte, optionally cast to text, varchar or character varying, so everything else is refused: a longer literal; a column or expression (including a column named both, Bug 311's shape), whose value is unknown until a row arrives; a one-character non-ASCII literal (over a binary column, VARBINARY/BLOB/bytea, the engines trim by byte); a hex or collated literal; NULL; and any other cast, such as a fixed-length char cast (on Postgres ' '::char(1) loses its trailing space and trims nothing). Some of those are refused conservatively where both engines would in fact agree. The likeliest real trigger is the Postgres whitespace idiom trim(both E' \t\r\n' from s), which on MySQL strips only exact repeats of that four-character run.

Remedy: for a generated column, supply a target-valid body with --expr-override <table>.<column>=<expression>; for a CHECK or index, drop it on the source and re-create the equivalent on the target after the run, or leave the table out with --exclude-table; for a view, leave it out with --exclude-view <name> and create the equivalent on the target. On Postgres a MySQL substring-trim can be written with regexp_replace; on MySQL a Postgres set-trim can be written as REGEXP_REPLACE(s, '^[xy]+|[xy]+$', ''). The column, CHECK and index refusals reach migrate, sync start's cold start and resume, schema add-table, restore, chain restore and CDC schema-forward; a view is refused at the views phase and in schema preview. Not refused, by design: non-unique expression indexes (they change no value and no accepted row), DEFAULT expressions (every migrated row carries its value explicitly), and same-engine runs. SQLite in either direction is outside this refusal: its own translator admits only one-argument trim. If you migrated MySQL ↔ Postgres with such an expression on an earlier release: compare generated columns and view results with the source's (sluice verify --depth count does not see it), search the target schema for trim(, rewrite each object for the target and re-copy the table.

Views: what a copied view reads (v0.160.4) #

sluice copies a view's body as text. MySQL writes the database name into every table reference of a stored view, and Postgres writes the schema name whenever it is not on the reading session's search path. Through v0.160.3 that body was copied verbatim, so a view landed in the target database or schema but kept naming the source's: where the target server also held a database or schema by that name (including the source itself, when both live on one server), the view read it at exit 0, returning another namespace's rows; otherwise it failed loudly (HIGH, silent, Bug 314, long-standing). Reached: MySQL → MySQL into a differently named database (including --map-database), Postgres → Postgres into a different schema (--target-schema, a target ?schema=, --map-schema, sync start), and Postgres → MySQL from a non-public schema. Since v0.160.4 the MySQL reader removes the source database's own qualifier, leaving string literals and references to any other database untouched; the Postgres reader reads view bodies with the source schema as the search path, so the source's own names come back unqualified and every other schema's qualified; and the Postgres writer runs each CREATE VIEW and CREATE MATERIALIZED VIEW with the target schema on the search path (public follows it only for a view read from public, whose extension calls are unqualified).

• VIEW-QUALIFIER-AMBIGUOUS (refusal). A MySQL view that also uses its own database's name as a table alias (FROM t app), a derived table's name or a CTE's name cannot have the qualifier removed safely: `app`.`id` may be the alias's column, and removing it could make a correlated subquery read another column without any error. sluice refuses such a view by name before creating any view. The check also over-refuses some safe uses: a column alias in GROUP BY/HAVING, a WINDOW name, or a CTE column list. Rename the alias in the source view (CREATE OR REPLACE VIEW), or leave the view out with --exclude-view <name> and create it on the target yourself after the run.
• VIEW-BODY-CROSS-ENGINE (refusal). A MySQL view going to Postgres or SQLite, and a Postgres view from a non-public schema going to MySQL or SQLite. Their qualifiers used to make the CREATE fail; with them removed, the text would parse on the other engine and could compute differently (MySQL's a / b is 3.5000 where Postgres and SQLite give 3; s = 'abc' matches 'ABC' under a MySQL case-insensitive collation and not elsewhere). Leave such a view out with --exclude-view <name> and create an equivalent on the target. A Postgres view from public, or a SQLite view, is copied verbatim as before; check its results on the target, because the same differences apply. This newly refuses a Postgres multi-schema fan-out into MySQL (and a restore of such a backup into MySQL), where a non-public view used to create because its qualifier named the MySQL database sluice had just created.

Corrections (Bug 316). The v0.160.4 notes said a name the run did not create (such as a table left out with --exclude-table) now fails loudly. That holds only when the target schema is not public and does not already hold a same-named object: if the target schema is public, or already holds a table by that name, the view binds to that target table at exit 0. A public → public run binds the same way on every release, and so does MySQL → MySQL now. And both refusals fire before any data is copied per database or schema, not per run: in a multi-namespace fan-out the namespaces processed before the refused one are already copied, so re-run with --exclude-view against a fresh target, or with --reset-target-data --yes for a sync. A Postgres view calling an extension installed outside public (a Supabase-style extensions schema) may now fail at the views phase; leave it out with --exclude-view and create it on the target.

Still open: a view over a different schema that the same run also renames (--include-schema app,public --map-schema app=app2 with a public view over app.t keeps naming app); and restoring a backup taken before v0.160.4, which replays its recorded, qualified bodies — take a fresh full backup on v0.160.4 or later if you restore into a differently named database or schema. If you migrated views that way on an earlier release: check each target view's definition (SELECT view_definition FROM information_schema.views WHERE table_schema = DATABASE() on MySQL, pg_get_viewdef on Postgres) and recreate any that names the source.

Columns in a non-UTF-8 character set: the change stream (v0.156.3) #

A legacy schema's CHAR/VARCHAR/TEXT columns are often declared latin1, cp1251, sjis, gbk, utf16 or another non-UTF-8 charset. They map to the same IR string type as a utf8mb4 column, and the value is always carried as UTF-8. The bulk copy (migrate, a sync cold start, a backup full) never sees the stored bytes: it reads over a utf8mb4 connection, so the server converts every value first. The change stream does see them — the binlog row image (MySQL, MariaDB) and the VStream row event (PlanetScale / Vitess) both carry a character column's value as the bytes stored in its own charset. Since v0.156.3 sluice converts those bytes by the column's charset, so sync, backup stream and backup incremental carry what the bulk copy carries: every 8-bit charset, latin1 as MySQL's cp1252 variant, the Japanese, Korean and GB charsets, the UTF-16/UTF-32/UCS-2 family, and swe7 (not ASCII-compatible: { is ä). Each conversion table is graded against the server's own CONVERT(… USING utf8mb4) over its whole code space on MySQL 8.0 and MariaDB 11.4. Keys and before-images are converted too, so a row updated or deleted by a non-ASCII text key is found on the target. A PlanetScale/Vitess column with a _bin collation (latin1_bin, utf8mb4_bin, …) arrives typed VARBINARY/BLOB/BINARY and is decoded as text by its collation; the declared column type decides, so a true binary column stays bytes.

What cannot be converted faithfully refuses with CHARSET-NOT-DECODABLE, naming the table, column and charset (the value is not printed):

• a non-ASCII big5 value — no available table matches MySQL's big5 (the common one disagrees in 267 places);
• on PlanetScale / Vitess, any non-ASCII value in a gbk, big5, tis620 or gb18030 column — vttablet sends those with collation 0, so the stream cannot tell the charset (the binlog lanes convert gbk, tis620 and gb18030 normally);
• the tis620 bytes MySQL itself converts to U+FFFD;
• on PlanetScale / Vitess, a non-UTF-8 ENUM/SET cell that is a member under both readings. vttablet sends such a cell as UTF-8 label text in CDC but as its stored bytes in the COPY phase, with identical field metadata, so sluice resolves each cell against the column's own labels; it refuses only when two labels are each other's mis-encoding (a latin1 ENUM('é','é')). Rename one label, or exclude the table. The index-0 '' is a member of every ENUM.

Pure-ASCII values in those columns stream normally. To proceed past the refusal, convert the column to utf8mb4 on the source and re-snapshot the table (sync … --restart-from-scratch), or exclude the table. Do not convert and resume: the stream replays history written in the old charset.

A charset change while the stream is behind it. A replay of history recorded before an ALTER … CHARACTER SET (a warm resume after downtime, a lagging stream, a cold-start handoff whose copy spanned the ALTER) carries bytes in the old charset.

• MySQL 8, and MariaDB under binlog_row_metadata=MINIMAL or FULL: each row event's TABLE_MAP records the collation the value was written in (MySQL 8 writes it under its default MINIMAL), and sluice decodes by it, so the replay is exact. Only a collation ID neither the source's collation table nor sluice can name refuses, with SLUICE-E-CDC-SCHEMA-REPLAY-MISMATCH.
• MariaDB under its default binlog_row_metadata=NO_LOG, and PlanetScale / Vitess, record no written charset (vttablet reports a replayed row's current collation). Rows are decoded by the current charset, and each table with non-UTF-8 text columns logs CHARSET-HISTORY-UNRECORDED once. When the stream reaches an ALTER that sets a non-UTF-8 charset explicitly (CONVERT TO CHARACTER SET, or MODIFY/CHANGE with a CHARACTER SET or COLLATE), sluice compares the charset and collation it sets with the shape it has been decoding the table by; a match means the stream is replaying, and it refuses with SLUICE-E-CDC-SCHEMA-REPLAY-MISMATCH. The rows replayed before that ALTER were already emitted, so the remedy depends on the command: under sync, re-snapshot the table (--restart-from-scratch); for backup stream / backup incremental, take a fresh full backup. A backup window that decoded rows by the new charset and ended before the ALTER is refused before it commits (CHARSET-HISTORY-UNRECORDED, SLUICE-E-CDC-SCHEMA-REPLAY-MISMATCH, take a fresh full backup). A change into utf8mb4/utf8mb3/utf8 is not guarded, so routine UTF-8 DDL never refuses a live stream; its replay carries the old bytes as v0.156.2 carried every value.

SQLite & Cloudflare D1 #

SQLite (and D1, which is SQLite over HTTP) is the one engine whose value storage isn't pinned by its column declaration — a column has a type affinity, and each stored value carries its own storage class. sluice resolves an IR type from the declared type in a load-bearing order: declared temporal / bool spellings win first, affinity second.

As a migrate target, SQLite emits the declared type its reader reads back to the same IR type. The one load-bearing wrinkle: an ir.Decimal is stored with TEXT affinity (the exact decimal string), not NUMERIC — NUMERIC affinity would coerce 19.99 to the binary float 19.989999999999998 and silently corrupt money (Bug 162); it reads back as text (a documented downgrade). Anything SQLite has no faithful storage for — geometry, inet/cidr/macaddr, bit, interval, array, domain — is refused loudly at emit time, never coerced to a silently-wrong column. D1 is not a write target: emit a SQLite .db (--target-driver sqlite) and wrangler d1 import it.

Column-fidelity markers you will see in the log #

Some schema facts have no spelling on the target, or have one that only some paths can use. sluice never drops one silently: each carries a grep-stable marker on a WARN line — or, where carrying on would land a wrong value, on the refusal that stops the run — so you can search a run's logs for it.

Per-column overrides #

The default policies cover the common case; override per column in YAML (mappings:) or on the CLI. Overrides are typed against the IR, not dialect syntax:

• --type-override TABLE.COLUMN=TYPE — force a target column type (repeatable). The override rewrites the IR type the reader decodes with, so e.g. =smallint on a TINYINT(1) reads the cell as an integer end-to-end.
• --enable-pg-extension EXT — opt into a Postgres extension type (hstore, citext, vector, PostGIS) so its columns pass through instead of refusing.
• YAML mappings: entries take table:, column:, target_type:, and free-form target_type_options: (e.g. binary: true) — the file form of --type-override. Zero-date policy is the process-global --zero-date error|null|epoch flag (per-sync via the sync run fleet config's zero-date key), not a per-column mapping.

Run sluice schema preview first to see the exact target DDL sluice would emit, including every widening/narrowing advisory and any untranslatable-expression refusal — before touching the target.