After the fsync abyss, something genial. DateStyle controls how PostgreSQL prints dates and how it resolves ambiguous date input, and the only thing you really need to know about it is: leave it on ISO, and the rest is interesting trivia.
The default is ISO, MDY. Context is user, with PGDATESTYLE as the environment variable libpq honors — the same pattern as application_name and client_encoding. Despite the single name, it’s two settings welded together, and that welding is the source of every bit of confusion this parameter generates.
Two parameters wearing one name
The first component is the output format: ISO, Postgres, SQL, or German. The second is the input ordering for ambiguous dates: MDY, DMY, or YMD. They’re nearly orthogonal, they’re set in one string separated by a comma, and the result confuses people endlessly. Tom Lane, asked about it on pgsql-novice, called the two-component design “a historical aberration, and I’d be the first to agree it’s ugly, but that’s how it works.” When the person who maintains it says that, you can stop trying to find the logic.
The confusion has a signature shape, which shows up in the mailing list archives over and over: someone wants their dates to print as month/day/year, so they SET datestyle = 'MDY' and nothing changes, because MDY is an input ordering, not an output format. There is no MDY output format. If you want US-style printed output you have to pick SQL or Postgres, which control format, and live with whatever they do. The two halves answer two different questions and setting one never affects the other.
What the values actually do
ISO prints 2026-06-08 — year-month-day, zero-padded, unambiguous, sortable as text, and identical regardless of anyone’s locale. The other three are historical: Postgres prints Sun Jun 08 2026, SQL prints 06/08/2026, and German prints 08.06.2026. The input ordering only matters when you feed PostgreSQL something genuinely ambiguous like 05/06/2026 — MDY reads that as May 6th, DMY as June 5th, and YMD would read 05/06/07 as 2005-06-07. Hand it an unambiguous ISO string and the ordering component never comes into play at all.
One wrinkle worth knowing: initdb doesn’t necessarily leave you on the built-in ISO, MDY default. It writes a DateStyle line into postgresql.conf matching the lc_time of the locale the cluster was initialized in, so a cluster created under a European locale may come up with DMY ordering. If you’ve ever wondered why two of your clusters disagree about what 01/02/2026 means, that’s where to look.
Leave the output on ISO. It is sortable, locale-independent, survives every round-trip through every client unchanged, and never makes anyone guess whether a date is American or sane. If an application genuinely needs a different printed format, that’s what to_char() is for — per query, explicit, and not a cluster-wide setting that quietly changes how every other application’s dates come out. The input ordering you can set to match your locale if you must, but the honest fix for ambiguous date input is to stop sending ambiguous dates: feed PostgreSQL ISO, and the question evaporates.