On Thu, Feb 29, 2024 at 2:38 PM Guyren Howe <guyren@xxxxxxxxx> wrote:
In case I’m not correct on the issues with these types, myself, what are the misconceptions, or where might I find them for myself?
My current understanding:
- character is fixed-length, blank-padded. Not sure when you’d want that, but it seems clear. Is the name just confusing?
Character is not stored fixed length, it is stored variable width but it just so happens that the variable length is the same for all rows.
- timestamptz is just converted to a timestamp in UTC. Folks might imagine that it stores the time zone but it doesn’t.
You are missing the point then whenever converting a stored timestamptz to textual representation the action is stable because it involves looking up the session time zone setting. The same goes in reverse unless the textual representation includes the time zone specification to use.
And yes, people do believe that whatever timezone was in effect when the data was recorded is saved when indeed it is not; such information would need to be captured separately.
- time with time zone *does* store the time zone, but this isn’t actually useful and should be avoided (I’m not entirely sure why and the docs only gesture at the problems without stating them, IIRC)
No it doesn't store the time zone. Nor do the docs say they do. And clearly point out the issue that evaluating a time zone without both date and time inputs is basically pointless.
> money is a fixed-point decimal value, the number of decimal places is locale determined. I’m not aware of any particular problems with that
You forget about the currency symbol dynamic. Like with time zones the local session provides the context, not the stored data.
David J.
