Rules for resolving a string that's intended to identify a UTC offset

The rules

Note: If the text contains a digit, then it is taken as POSIX syntax. See the appendix B.5. POSIX Time Zone Specifications in the PostgreSQL documentation.

When a string is used to identify a UTC offset, there might seem a priori to be three contexts in which it might be resolved:

either as a pg_timezone_names.name value
or as a pg_timezone_names.abbrev value
or as a pg_timezone_abbrevs.abbrev value

Lest "seem" might leave you guessing, Rule 2 says that not all contexts are used to resolve all lookups.

Here are the rules for resolving a string that's intended to identify a UTC offset:

Rule 1 — Lookup of the string is case-insensitive (discounting, of course, using an explicit select statement from one of the pg_timezone_names or pg_timezone_abbrevs catalog views).
Rule 2 — The string is never resolved in pg_timezone_names.abbrev.
Rule 3 — The string is never resolved in pg_timezone_abbrevs.abbrev as the argument of set timezone but it is resolved there as the argument of timezone() and within a text literal for a timestamptz value.
Rule 4 — The string is resolved first in pg_timezone_abbrevs.abbrev and, only if this fails, then in pg_timezone_names.name. This applies only in those syntax contexts where pg_timezone_abbrevs.abbrev is a candidate for the resolution—so not for set timezone, which looks only in pg_timezone_names.name.

The syntax contexts of interest are described in the section Three syntax contexts that use the specification of a UTC offset.

Note: The code that substantiates Rule 4 is able to do this only because there do exist cases where the same string is found in both resolution contexts but with different utc_offset values in the two contexts.

This table summarises Rule 2, Rule 3, and Rule 4:

Syntax context \ View column	pg_timezone_names.name	pg_timezone_names.abbrev	pg_timezone_abbrevs.abbrev
set timezone^[1]	here only Rule 3	never Rule 2	not for set timezone
at time zone^[2]	second priority Rule 4	never Rule 2	first priority
timestamptz value ^[3]	second priority Rule 4	never Rule 2	first priority

Note 1: This row applies for the two alternative syntax spellings:

  set timezone = 'Europe/Amsterdam';
  set time zone 'Europe/Amsterdam';

Note 2: This row applies for both the operator syntax and the function syntax:

     select (
         (select '2021-06-02 12:00:00'::timestamp at time zone 'Europe/Amsterdam') =
         (select timezone('Europe/Amsterdam', '2021-06-02 12:00:00'::timestamp))
       )::text;

You usually see the operator syntax in blog posts and the like. But there are good reasons to prefer the function syntax in industrial strength application code. The section Recommended practice for specifying the UTC offset explains why and encourages you to use the overloads of the timezone() built-in function only via the user-defined wrapper function at_timezone().

Note 3: This row applies for both the ::timestamptz typecast of a text literal and the invocation of the make_timestamptz() built-in function:

     select (
         (select '2021-06-02 12:00:00 Europe/Amsterdam'::timestamptz) =
         (select make_timestamptz(2021, 6, 2, 12, 0, 0, 'Europe/Amsterdam'))
       )::text;

Summary

The rules for resolving a string that's intended to specify a UTC offset can be summarized thus:

The resolution of a string is case-insensitive.
A string is never resolved in pg_timezone_names.abbrev.
A string is always resolved in pg_timezone_names.name.
A string used in set timezone is resolved only in pg_timezone_names.name.
A string that's used in at time zone or in the explicit specification of a timestamptz value is resolved first in pg_timezone_abbrevs.abbrev and only if this fails, then in pg_timezone_names.name.
If a string escapes all of the attempts at resolution that the previous five bullet points set out, then an attempt is made to resolve it as POSIX syntax.