MySQL sql_mode vs PostgreSQL Strictness: What Breaks, What Gets Safer

Every database encodes a philosophy about bad input. MySQL's historical answer was to accept it, adjust it, and warn; PostgreSQL's answer has always been to reject it. Neither position is absurd: MySQL grew up powering write-heavy web apps where losing an insert was worse than storing a truncated string, and PostgreSQL grew up as the correctness-first system where a wrong answer was worse than an error.

The reason this matters for your migration is that MySQL's behavior is not one thing. It is a function of sql_mode, a server and session variable whose defaults changed dramatically over the years. Understanding that history tells you which of your application's habits were formed under loose mode, which are already strict-safe, and exactly what PostgreSQL will refuse to tolerate.

A short history of sql_mode

For most of MySQL's life, the default sql_mode was effectively permissive. Insert a 300-character string into a VARCHAR(255) and it was silently truncated with a warning almost nobody read. Insert an out-of-range number and it was clamped to the nearest representable value. Insert an invalid date and you got the famous zero date, 0000-00-00, a value that exists in no calendar. Divide by zero and you got NULL plus a warning. GROUP BY let you select non-aggregated columns and picked an arbitrary row for them.

MySQL 5.7, released in 2015, flipped the defaults: STRICT_TRANS_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO, and ONLY_FULL_GROUP_BY all became standard, and MySQL 8.0 kept them. Modern, well-configured MySQL is genuinely strict about most of this. Credit where due: the MySQL team executed a difficult multi-year defaults migration.

But here is the operational truth I have seen across many fleets: sql_mode is configurable per server and per session, and legacy applications frequently pin the old behavior to avoid breakage. A single SET SESSION sql_mode = '' in a shared library, an ORM compatibility shim, or a config template copied since 2013 means your data may have been written under loose rules regardless of your server version. The database's contents reflect the loosest mode that ever wrote to them.

PostgreSQL has no dial

PostgreSQL's equivalent of sql_mode does not exist. A string that exceeds varchar(255) is an error, every time, in every session, and there is no setting to change that. An invalid date is an error. Division by zero is an error. A non-aggregated column in a grouped query is an error unless it is functionally dependent on the GROUP BY columns (PostgreSQL recognizes primary-key dependence, much like ONLY_FULL_GROUP_BY does). Out-of-range numerics are errors, not clamps.

PostgreSQL is also strict about types in expressions, and it was not always maximally so: version 8.3, back in 2008, famously removed a set of implicit casts to text, breaking queries that compared numbers to strings, and the project took the breakage to get correctness. Today, comparing an integer column to an integer-looking string works via well-defined casts, but nonsense coercion does not. In MySQL, the expression '1abc' + 1 yields 2 with a warning; in PostgreSQL, casting '1abc' to a number is an error. There is exactly one behavior, so there is exactly one thing to test.

-- PostgreSQL: each of these fails loudly instead of adjusting your data.
INSERT INTO users (username) VALUES (repeat('x', 300));
-- ERROR: value too long for type character varying(255)

SELECT '2026-02-30'::date;
-- ERROR: date/time field value out of range: "2026-02-30"

SELECT 10 / 0;
-- ERROR: division by zero

What breaks when you migrate

The strictness gap surfaces in two waves: at data load, and at query time. Plan for both.

• Zero dates will not load. 0000-00-00 is not a valid PostgreSQL date. Decide per column whether it means NULL or a sentinel like -infinity, and convert during extraction. Do this deliberately; zero dates almost always encode "unknown," and NULL is usually the honest answer.
• Out-of-range and truncated data is already damaged. Values MySQL clamped or truncated load fine, because they were altered at write time. The migration will not fail; the data is simply shorter or capped than the application intended. Worth an audit if any column feeds billing or compliance.
• Implicit-coercion queries fail. WHERE numeric_col = 'abc' returns an error, not an empty set. String-to-number comparisons across join keys, a classic MySQL schema smell where one side is VARCHAR, need explicit casts or, better, a column type fix.
• Operator meanings shift. In stock MySQL, || is logical OR; in PostgreSQL it is string concatenation (as in the standard, and as MySQL itself offers via PIPES_AS_CONCAT). Double quotes mean identifiers in PostgreSQL, not string literals. Backtick quoting does not exist.
• Grouped queries need discipline. If your fleet predates ONLY_FULL_GROUP_BY or disables it, expect a batch of GROUP BY queries to need rewriting with aggregates, DISTINCT ON, or window functions. DISTINCT ON in particular replaces the "MySQL picks a row" pattern with a defined, ordered choice.
• ENUM and type edges differ. MySQL ENUMs accept the empty string under loose mode when given invalid input; PostgreSQL enum types reject unknown labels outright. Audit enum columns for empty-string values before mapping them.

The right tool for the query-time wave is your test suite plus a shadow environment, because grep will not find every implicit coercion. The right tool for the data wave is profiling queries run against MySQL before extraction: count zero dates, find max lengths at column limits (a spike of values at exactly 255 characters is truncation's fingerprint), and list enum anomalies.

-- Run against MySQL before migrating: truncation fingerprints.
SELECT count(*) AS suspiciously_exact
FROM users
WHERE char_length(bio) = 255;

-- Zero-date inventory for one table:
SELECT count(*) AS zero_dates
FROM orders
WHERE shipped_at = '0000-00-00 00:00:00';

What gets safer, permanently

The payoff side of the ledger is substantial. Whole bug classes stop being possible. Silent truncation cannot corrupt a value on the way in; the application hears about it at the INSERT, in the request where the oversized value originated, with a stack trace pointing at the cause. Arithmetic on garbage input cannot produce a plausible-looking wrong number. A date that never existed cannot enter the system and later crash a report. Type mismatches surface in development, not as production data drift discovered a year later.

There is also an organizational effect I want to name: with PostgreSQL there is no strictness configuration to audit, so environments cannot disagree about it. On MySQL fleets, staging-strict versus production-loose (or the reverse) is a recurring source of "works in staging" incidents. PostgreSQL removes the variable entirely. Combined with strong constraint tools (CHECK constraints, exclusion constraints, custom domains with validation), the database becomes an active participant in data quality rather than a warning log nobody tails. The PostgreSQL overview and comparison pages cover more of these philosophical differences; the time-handling equivalent of this topic, timestamp vs timestamptz, deserves its own read since MySQL and PostgreSQL disagree about time zones too.

Migration tactics that respect both systems

Do not try to make PostgreSQL forgiving; make the pipeline explicit instead. Convert zero dates during extraction, not with a loosened target. Fix join-key type mismatches in the schema rather than sprinkling casts. Rewrite loose GROUP BY queries with real semantics rather than mechanical DISTINCT wrappers. Where MySQL's clamping was actually load-bearing (user-supplied lengths, for example), enforce limits in the application and add a CHECK constraint so the rule is visible in the schema. Expect the first integration test run against PostgreSQL to fail loudly, and treat every failure as the strictness dividend being paid early, in test, rather than late, in production data.

Strict databases still need watching: where MonPG fits

Strictness changes the error profile of your application: instead of warnings you never saw, you get real errors, and post-migration the error rate in PostgreSQL server logs is one of the highest-signal indicators you have. A spike of value-too-long errors after a deploy points at a code path that still assumes truncation. Constraint-violation patterns show data quality issues at their entry point.

MonPG monitors PostgreSQL exclusively, keeping error patterns, query history from pg_stat_statements, lock behavior, and autovacuum health in one PostgreSQL-native workflow. After a MySQL migration, that history is what turns "something started failing" into "this query family, since this deploy, with this error class." Set it up before cutover so your baseline predates your traffic. Start with the PostgreSQL monitoring guide.