Why is string literal '2021-02-16 09:00' without timezone information interpreted as `timestamp with time zone` with AT TIME ZONE?

Question

set timezone = 'Asia/Ho_Chi_Minh';

I set times displayed to GMT+7.

select '2021-02-16 09:00' AT TIME ZONE 'Asia/Singapore';
select '2021-02-16 09:00+06' AT TIME ZONE 'Asia/Singapore';

Output

2021-02-16 10:00:00
2021-02-16 11:00:00

Because AT TIME ZONE converts timestamp without time zone to timestamp with time zone and vice versa (https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT), and because both outputs are timestamp without time zone (you can see they don't have + - part, or pg_typeof to confirm), i infer that both string literals are interpreted as timestamp with time zone.

I understand the 2nd select has the +06 part to indicate time zone which makes sense.

Question:

Why does the 1st select which has no + or - time zone part get interpreted as timestamp with time zone like the 2nd select?

---

I know I can specify type with TIMESTAMP/TIMESTAMP WITH TIME ZONE or cast with ::timestamp/::timestamptz, but assuming i don't do any of that, is there any docs describing what's going on to the string literal?

Answer 1

It's because of type preference. For at time zone construct to work, the unknown literal you specified needs to be timestamp, timeTZ or timestampTZ - to break the tie between these and pick one, Postgres checks pg_type.typispreferred:

typispreferred bool

True if the type is a preferred cast target within its typcategory.

TimestampTZ is the preferred type in that group:
_{demo at db<>fiddle}

select typname
      ,typcategory 
      ,typispreferred 
from pg_type 
where typname in('timetz','timestamp','timestamptz');

typname	typcategory	typispreferred
timestamp	D means "Date/time types"	f
timestamptz	D	T
timetz	D	f

Postgres rewrites at time zone as a pg_catalog.timezone() function call. It's overloaded and one of the variants available accepts timestampTZ as the second param. Unless you explicitly assign a type to the literal and pick timestamp, Postgres has to guess and pick one for you, following the predefined preference of timestampTZ.
This means in both your examples you're starting with a timestampTZ, which is then stripped by at time zone, returning a shifted timestamp without time zone.

As a side note, it should be your preferred type, too.

---

There's actually a similar problem concerning 'Asia/Singapore' on the right. There are variants of pg_catalog.timezone() that accept an interval instead of text there, but since those are not only different types but also types of different categories, Postgres has to break the category tie: if available, 'S' "String types" are preferred so text wins. This bias towards string is explained in Chapter 10. Type Conversion, 10.3. Functions, step 4.e:

If any input arguments are unknown, check the type categories accepted at those argument positions by the remaining candidates. At each position, select the string category if any candidate accepts that category. (This bias towards string is appropriate since an unknown-type literal looks like a string.)