Booleans are the smallest possible data type and somehow still a migration topic. MySQL has never had a true boolean column type: BOOLEAN in MySQL DDL is an alias for TINYINT(1), a one-byte integer wearing a costume. PostgreSQL has had a genuine boolean type forever. Converting between them is trivial in the happy case, and the unhappy cases are exactly the ones you will meet in a fifteen-year-old production database.
This post covers the whole path: what TINYINT(1) actually is, the display-width deprecation in MySQL 8 that confused a generation of connectors, the full integer type-mapping table for a migration, and the ORM and constraint details that decide whether your booleans arrive in PostgreSQL as booleans or as small integers with commitment issues.
What TINYINT(1) actually is
TINYINT is a one-byte integer, ranging from -128 to 127 signed. The (1) is not a size, a range, or a constraint; it is a display width, a hint that command-line clients could once use for padding output. TINYINT(1) stores 7 and -5 as happily as it stores 0 and 1. MySQL's boolean semantics are conventions layered on top: 0 is false, any nonzero value is true, and the BOOLEAN keyword in DDL quietly becomes tinyint(1) in SHOW CREATE TABLE.
In practice the convention works fine, which is worth acknowledging: millions of applications run on TINYINT(1) booleans without incident, because well-behaved code only ever writes 0 and 1. But the type system is not enforcing anything, and long-lived databases accumulate exceptions. A bulk update that wrote 2, an increment used as a "count of times flagged" that someone later treated as a flag, an import from a system with different conventions. Since MySQL 8.0.16, CHECK constraints are actually enforced (before that they were parsed and ignored), so newer schemas can pin values to 0 and 1, but most schemas predate the habit.
The display-width deprecation, briefly
MySQL 8.0.17 deprecated integer display widths, and 8.0.19 stopped showing them in SHOW CREATE TABLE output for most integer types. The exception that proves how load-bearing this convention became: TINYINT(1) is still displayed, specifically because the ecosystem uses it as the boolean marker. Connector/J maps TINYINT(1) to java.lang.Boolean by default (the tinyInt1isBit parameter), ORMs across every language sniff for it, and schema-diff tools treat tinyint(1) and tinyint as different declarations. When the display width otherwise disappeared, tooling that keyed on INT(11) broke in small annoying ways; the boolean special case was kept alive deliberately.
I mention this not to dunk on MySQL but because it clarifies the migration stakes: your boolean semantics currently live in a display-width convention interpreted by client libraries. Moving to PostgreSQL moves them into the type system, which is strictly better, provided the conversion is done deliberately.
PostgreSQL's boolean is a real type
PostgreSQL's boolean holds exactly three states: true, false, and NULL. Input is flexible (true, yes, on, 1, and their opposites, in several spellings), storage is one byte, and output is t or f at the SQL level, with drivers presenting native language booleans. Nothing else can be stored in the column, ever, in any session. Comparison and indexing understand the type: WHERE is_active works with no = true noise, and NOT is_active is self-documenting.
Two idioms are worth adopting on arrival. First, partial indexes: boolean columns are usually skewed (2% of rows unshipped, 98% shipped), and an index on the rare state is tiny and fast. Second, boolean aggregates like bool_or and bool_and, plus FILTER clauses, replace the SUM(flag) arithmetic habits from MySQL.
-- A partial index that only stores the interesting minority:
CREATE INDEX orders_unshipped_idx
ON orders (created_at)
WHERE NOT shipped;
-- Boolean aggregation without integer arithmetic:
SELECT customer_id,
bool_or(is_disputed) AS has_dispute,
count(*) FILTER (WHERE NOT shipped) AS open_orders
FROM orders
GROUP BY customer_id;The migration mapping table
Booleans travel with their integer siblings, so here is the mapping I use for MySQL integer types, with the boolean row being the one judgment call:
| MySQL type | PostgreSQL type | Notes |
|---|---|---|
| TINYINT(1) | boolean | Only if the column is semantically boolean; verify values first |
| TINYINT | smallint | PostgreSQL has no 1-byte integer; smallint is the floor |
| SMALLINT | smallint | Direct match |
| MEDIUMINT | integer | No 3-byte type; integer covers the range |
| INT | integer | Direct match if signed |
| INT UNSIGNED | bigint | PostgreSQL has no unsigned types; the top half needs bigint |
| BIGINT | bigint | Direct match if signed |
| BIGINT UNSIGNED | numeric(20,0) | Values above the signed 64-bit max exist only here |
The unsigned rows deserve emphasis: UNSIGNED is the other place MySQL integer habits do not port, and mechanically mapping INT UNSIGNED to integer will eventually overflow. Add a CHECK (col >= 0) in PostgreSQL if the non-negativity was meaningful.
For the boolean conversion itself, decide what nonzero values mean before you cast. The safe default treats any nonzero as true, matching MySQL's own comparison semantics:
-- First, audit for surprises in the source data (run on MySQL):
SELECT is_active, count(*) FROM users GROUP BY is_active;
-- Then convert in PostgreSQL after a raw integer load:
ALTER TABLE users
ALTER COLUMN is_active TYPE boolean
USING is_active <> 0;If the audit shows values other than 0, 1, and NULL, stop and ask what they meant. Sometimes it is dirt; sometimes it is an undocumented third state that needs to become a real enum or status column instead of a boolean. This is a data-modeling decision disguised as a cast, and note that the USING conversion rewrites the table under an exclusive lock; on big tables, plan it like the schema change it is (see ALTER TABLE locks).
ORM mappings: where boolean bugs actually live
Most boolean migration incidents are ORM-shaped. The classic pattern: the ORM was configured for MySQL, where the boolean column is an integer at the wire level, and application code leaked integer assumptions: comparisons to 1, arithmetic on flags, serialization that emits 0/1 into JSON contracts that downstream consumers now depend on.
- Django abstracts this fully; BooleanField becomes a real boolean on PostgreSQL. Watch raw SQL and .extra() remnants that say flag = 1.
- Rails historically stored booleans as tinyint on MySQL and handles native boolean on PostgreSQL transparently; the risk is in find_by_sql strings and serialized column hacks.
- SQLAlchemy maps Boolean to native boolean on PostgreSQL; on MySQL it emits tinyint with an optional CHECK. Explicit type decorators written for MySQL sometimes hardcode Integer and need removal.
- JPA/Hibernate depends on dialect and the tinyInt1isBit connector behavior; after migration, verify the column maps to boolean and not a numeric type with a converter stack nobody remembers writing.
- Go and hand-written SQL need the most attention, since scanning a PostgreSQL boolean into an int fails where the MySQL driver happily produced integers.
The audit technique is boring and effective: grep for the column names alongside = 1, = 0, != 1, and integer casts, and run the test suite against PostgreSQL before cutover. API responses that used to serialize 0/1 deserve an explicit compatibility decision rather than an accidental change to true/false. For the wider migration picture beyond types, the comparison pages and the PostgreSQL overview are the map.
After cutover: MonPG on your PostgreSQL fleet
Type migrations have a performance tail. A boolean column that arrived without its partial index, a query family that regressed because a MySQL-tuned predicate no longer matches the new plan, or a table rewrite from ALTER COLUMN TYPE that left bloat behind: all of it shows up in query behavior in the first weeks on PostgreSQL, and only if someone is looking.
MonPG monitors PostgreSQL only, which means the entire workflow is built around PostgreSQL evidence: pg_stat_statements history to compare query families before and after each deploy, index usage to catch the missing partial index, plan context and wait events for the regressions, and autovacuum tracking for the post-rewrite bloat. Put the baseline in place the day the migration lands so week-two questions have week-one answers. The PostgreSQL monitoring guide is the place to start.