Re: BUG #13267: Some timezones in pg_timezone_names are missing in pg_timezone_abbrevs

Berend De Schouwer <berend(dot)de(dot)schouwer(at)gmail(dot)com> writes:
> I was just surprised to see that the list of abbrevs in _names is
> different from the list of abbrevs in _abbrevs. I would have expected
> the lists to be identical.

Unfortunately not. If you look a bit closer you will note that many
time zones use the very same abbreviation for entirely different UTC
offsets, so we could not possibly make those mappings be inverses.
The point of the abbreviation customization facilities is to let the
database user decide which meaning he'd like to assign to debatable
abbreviations.

Another reason for limiting what the server will accept is to reduce
the scope for errors: even though there is only one meaning for "CAT"
anywhere in the world, if you are not in Africa then it's quite likely
that such a string is a typo (perhaps for "CST", which is only one key
away) and not intended input.

> It makes for unexpected bugs when the OS, Java and the DB have different
> locale and zoneinfo datasets.

One thing you can do to cut down the variation is to use
--with-system-tzdata at configure time, so that the DB automatically uses
the OS' zoneinfo. But I don't know of any standardized answer to the
conflicting-abbreviations problem.

regards, tom lane