"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes: > On Tuesday, May 18, 2021, Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote: >> Am I missing an essential clue to resolving what seems to me to be a >> paradox? Or am I seeing two kinds of bug? > You are missing the material in appendix B.4 > https://www.postgresql.org/docs/current/datetime-config-files.html The short answer is that the reason there are two views is that there are two sources of truth involved. pg_timezone_names reflects the zone names defined in the IANA timezone database, while pg_timezone_abbrevs reflects the abbreviations defined in our user-customizable abbreviations table. It'd be impossible to make them match exactly, and we don't try exceedingly hard. In particular, the IANA list has some zones such as "CET" that don't follow their own continent/city naming convention. (AFAIK those are all legacy zones that they'd get rid of if they weren't concerned with backwards compatibility.) If those look like abbreviations, which they mostly do, then it's confusing. Where the rubber meets the road is in timestamptz input, and there we consult the abbreviations table first. (Not sure if that's documented, but you can easily prove it by experiment.) As for the question about "abbreviations" like +09 --- those are not abbreviations at all, they're just hard-coded numeric UTC offsets. So they don't appear in pg_timezone_abbrevs. IANA uses those as display offsets in zones where there's not any widely-used-on-the-ground abbreviation. regards, tom lane
