Re: Content for talk on Postgres Type System at PostgresConf

On Thu, Feb 29, 2024 at 2:38 PM Guyren Howe <guyren(at)gmail(dot)com> 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.