Rule 1

A string that's intended to identify a UTC offset is resolved case-insensitively. (This rule excludes, of course, selecting explicitly from one of the pg_timezone_names or pg_timezone_abbrevs catalog views).

The anonymous PL/pgSQL block below looks up the name America/Costa_Rica, spelled exactly with that mix of case, in pg_timezone_names and returns its abbreviation. Because an ordinary query with a case-sensitive predicate is used, this confirms the canonical status of the spelling.

The canonically spelled name America/Costa_Rica is also used to return utc_offset from pg_timezone_names. And the abbreviation is used to return utc_offset from pg_timezone_abbrevs. It isn't always the case that a timezone abbreviation from pg_timezone_names is found in pg_timezone_abbrevs. But the fact that the query used here returns a value without error shows that, in this case, a match is found. An assert statement tests that the two possibly different offset values agree. (This, too, isn't always the case.)

Another assert statement confirms that the abbreviation is not found as a name in pg_timezone_names. (This sometimes is the case.)

Four different variants of the timezone name, each spelling it with a different mix of case, are defined.

An assert statement ("Assert #1") confirms that invoking current_setting('TimeZone') returns the canonical spelling when the timezone is set using one of the case-variants of the timezone name.

Another three case-variants are used, each in a different syntax context, to produce a timestamptz value. And assert statements ("Assert #2" and "Assert #3") are used to confirm that the three differently-produced timestamptz values are all the same.

Finally, the result of the lower() built-in function on the abbreviation is used to construct a timestamptz literal; and the upper() result is used as the at time zone argument. And assert statements ("Assert #4" and "Assert #5") are used to confirm that these two timestamptz values are the same as the first three.

Try the block like this:

do $body$
  -- The spelling of "nm" is the canonical one, as is recorded in "pg_timezone_names"
  -- and as is returned by "current_setting('TimeZone')".
  nm constant text     not null := 'America/Costa_Rica';

  -- Case variants. Each is used once in a different syntax setting.
  n1 constant text     not null := 'AMERICA/COSTA_RICA';
  n2 constant text     not null := 'america/costa_rica';
  n3 constant text     not null := 'AMERICA/costa_rica';
  n4 constant text     not null := 'america/COSTA_RICA';

  ab constant text     not null := (select abbrev     from pg_timezone_names   where name   = nm);
  on constant interval not null := (select utc_offset from pg_timezone_names   where name   = nm);
  oa constant interval not null := (select utc_offset from pg_timezone_abbrevs where abbrev = ab);
  c  constant int      not null := (select count(*)   from pg_timezone_names   where name   = ab);


  set_timezone  constant text not null := $$set timezone = '%s'$$;
  tz_on_entry   constant text not null := current_setting('TimeZone');
  assert oa = oa,    'name/abbrev UTC offsets disagree';
  assert c  = 0,     'Abbrev found in';

    lower_ab  constant text not null := lower(ab);
    upper_ab  constant text not null := upper(ab);
    dt_text   constant text not null := '2021-05-15 12:00:00';
    t0        constant timestamptz not null := make_timestamptz(2021, 5, 15, 12, 0, 0, n1);
    execute format(set_timezone, n2);
      current_setting('timezone') = nm,
      'Assert #1 failed';

      (dt_text||' '||n3)::timestamptz = t0,
      'Assert #2 failed';

      (dt_text::timestamp at time zone n4) = t0,
      'Assert #3 failed';

      (dt_text||' '||lower_ab)::timestamptz = t0,
      'Assert #4 failed';

      (dt_text::timestamp at time zone upper_ab) = t0,
      'Assert #5 failed';

  execute format(set_timezone, tz_on_entry);

The block finishes silently.

This outcome supports the formulation of the rule that this page addresses.