Yes, the SQL syntax is full of misnomers. I agree it should have more hazard tape around it.
The type qualifier "with timezone" doesn't mean it stores a timezone with it! It means the input is interpreted with timezone offset, producing an unambiguous moment on the timeline. You can then compare all such values with a total ordering. But, these stored values do not preserve any offset/locale information. You can't ask "what was the offset of this timestamp when it was input?" That denormalized locale information is stripped when it is interpreted.
The naive type (without timezone) really means that the timezone information is absent and the interpretation is to be deferred. You have to supply timezone information before it can be resolved to the timeline. This is what the implicit coercion is doing in PostgreSQL, mixing in either the session or server timezone offset.
The above is further complicated in that PostgreSQL will supply an implicit (session or server) timezone during interpretation of the input for timestamp with time zone. And conversely, it will ignore timezone or offset even if present in the input for a naive timestamp! I think both of these would be better off handled as type/input errors in a strict mode.
The poorly named "(ts)::timestamptz at time zone 'tz'" construct is the inverse of the input transform that takes a naive timestamp and the given timezone to produce the known moment.
The other sad bit is that all of this is naive about the difference between UTC, TAI, and Unix time standards. There is ambiguity in postulating any future time, since the exact presence of leap seconds is not yet determined.
The type qualifier "with timezone" doesn't mean it stores a timezone with it! It means the input is interpreted with timezone offset, producing an unambiguous moment on the timeline. You can then compare all such values with a total ordering. But, these stored values do not preserve any offset/locale information. You can't ask "what was the offset of this timestamp when it was input?" That denormalized locale information is stripped when it is interpreted.
The naive type (without timezone) really means that the timezone information is absent and the interpretation is to be deferred. You have to supply timezone information before it can be resolved to the timeline. This is what the implicit coercion is doing in PostgreSQL, mixing in either the session or server timezone offset.
The above is further complicated in that PostgreSQL will supply an implicit (session or server) timezone during interpretation of the input for timestamp with time zone. And conversely, it will ignore timezone or offset even if present in the input for a naive timestamp! I think both of these would be better off handled as type/input errors in a strict mode.
The poorly named "(ts)::timestamptz at time zone 'tz'" construct is the inverse of the input transform that takes a naive timestamp and the given timezone to produce the known moment.
The other sad bit is that all of this is naive about the difference between UTC, TAI, and Unix time standards. There is ambiguity in postulating any future time, since the exact presence of leap seconds is not yet determined.