Re: The contents of the pg_timezone_names view bring some surprises

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Tuesday, May 18, 2021, Bryn Llewellyn <bryn(at)yugabyte(dot)com> 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