Re: The contents of the pg_timezone_names view bring some surprises

On 2021-05-22 08:26:27 -0700, Adrian Klaver wrote:
> On 5/22/21 3:09 AM, Peter J. Holzer wrote:
> > On 2021-05-19 06:57:13 -0700, Adrian Klaver wrote:
> > > On 5/18/21 11:31 PM, Bryn Llewellyn wrote:
> > > > This claims (as I read it) that a time zone abbreviation uniquely
> > > > determines an offset from UTC.
> > >
> > > It says no such thing
> >
> > Maybe that's the inherent ambiguity of the English language, but to me
> > "Such a specification defines a particular offset from UTC" does imply a
> > one-to-one mapping from abbreviation to offset.
>
> As your later post points out the reality is a given abbreviation has only
> one offset, but an offset may have many abbreviations.

Which is what Bryn wrote. (But I realize I'm getting into an argument
about what another person meant - again. I should stop that. If Bryn
thinks it is important to be understood he can explain himself.)

> > > and would be lying if it did.
> >
> > As far as pg_timezone_abbrevs is concerned, it is correct. abbrev is
> > unique in that view:
>
> 1) Go to Postgres cluster and in ~/share/timezonesets search in Defaults.txt
> for the word C(c)onflict.
>
> 2)Then go here
> https://www.postgresql.org/docs/current/datetime-config-files.html and see
> how the below query could be made to change if someone is not aware of 1).

Actually, no. If you introduce a conflict in a timezoneset file the
database doesn't even start:

2021-05-22 19:47:17 CEST [399103]: [1-1] user=,db=,pid=399103 FATAL: time zone abbreviation "test" is multiply defined
2021-05-22 19:47:17 CEST [399103]: [2-1] user=,db=,pid=399103 DETAIL: Entry in time zone file "Default", line 634, conflicts with entry in file "Default", line 635.
pg_ctl: could not start server

So abbrev is a unique key on pg_timezone_abbrevs and the documentation
is correct.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"