Rule 1
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$
declare
-- 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');
begin
assert oa = oa, 'name/abbrev UTC offsets disagree';
assert c = 0, 'Abbrev found in pg_timezone_names.name';
declare
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);
begin
execute format(set_timezone, n2);
assert
current_setting('timezone') = nm,
'Assert #1 failed';
assert
(dt_text||' '||n3)::timestamptz = t0,
'Assert #2 failed';
assert
(dt_text::timestamp at time zone n4) = t0,
'Assert #3 failed';
assert
(dt_text||' '||lower_ab)::timestamptz = t0,
'Assert #4 failed';
assert
(dt_text::timestamp at time zone upper_ab) = t0,
'Assert #5 failed';
end;
execute format(set_timezone, tz_on_entry);
end;
$body$;
The block finishes silently.
This outcome supports the formulation of the rule that this page addresses.