Skip to main content

PG.L6.STANDARD_CONFORMING_STRINGS_OFF — the same migration builds two different databases

  • Category: safety
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

With standard_conforming_strings = on — the standard behavior, and the default for many years — a backslash inside an ordinary single-quoted string is just a backslash. With it off, it escapes the character after it: a two-character sequence written as backslash-n becomes a newline, and a Windows path loses whatever its next letter escapes to.

Nothing raises an error. The row simply holds something other than what the migration said, which means the same migration file applied to two servers produces two different databases. That is the reason this sits in the safety category rather than among the idioms.

Where the setting comes from​

PostgreSQL files it among the version and platform compatibility settings, which is its own statement about it: it exists to keep pre-standard code running, not as a choice a new deployment makes. Finding it off on a modern cluster almost always means an old configuration carried forward rather than a decision.

The session is not the server​

The setting is session-settable, and an older tool or a legacy connection may turn it off for itself. That is not a server finding and is not reported. The check reads what the server hands a new connection, never what this one happens to be running with — so a server correctly set to on stays silent even when the audit's own session has it off.

Flagged​

ALTER SYSTEM SET standard_conforming_strings = off;
SELECT pg_reload_conf();

Preferred​

ALTER SYSTEM SET standard_conforming_strings = on;
SELECT pg_reload_conf();

If you genuinely need escape semantics​

Ask for them per literal, where the reader can see it, instead of changing what every literal in the system means:

SELECT E'first line\nsecond line';

The E'' syntax is explicit, local, and unaffected by the server setting.

Sources​