From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Guyren Howe <guyren@gmail.com>
Cc: Postgres General <pgsql-general@postgresql.org>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Content for talk on Postgres Type System at PostgresConf
Date: Thu, 29 Feb 2024 17:51:11 -0500
Message-ID: <2758726.1709247071@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwbOXtDcYMM5SMbUNoxOSLCEjEgLG7qwnG2h15Vs51X69Q@mail.gmail.com>
References: <F9A4875C-503C-4D1F-8AA6-0C204F60D3F4@gmail.com>
<08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at>
<372fd5d1-8d7c-411e-8a35-41793978e74b@Spark>
<CAKFQuwbOXtDcYMM5SMbUNoxOSLCEjEgLG7qwnG2h15Vs51X69Q@mail.gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Feb 29, 2024 at 2:38 PM Guyren Howe <guyren@gmail.com> wrote:
>> 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.
It's nastier than that: the restriction is that the length in
*characters* be the same in all rows. In a variable-width encoding
like UTF8 the length in bytes can vary. This means that the
optimization that CHARACTER was meant to enable (ie "the physical
width of this column is fixed, so you don't have to store a length
indication") doesn't work for us, making it just about totally
useless.
BTW, the SQL spec words the constraint the same way, making me wonder
if any modern RDBMS can use this type in the way it was designed.
>> - 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.
timetz *does* store a time zone, in the sense of storing a numeric
offset from UTC (i.e., "so many minutes east or west of Greenwich").
The problem is that in most real-world applications your notion of
"time zone" probably includes annual DST changes, which timetz can't
represent. I don't say the type is completely useless, but its
usefulness is a lot less than you might guess.
>> 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.
Yeah. The fact that the same stored value might look like 10.00 euros
to one session and 1000 yen to another one is pretty catastrophic.
The other nasty thing about money is that it's an int64 so it can't
represent more than 2^63 pennies (for whatever a "penny" is). Now,
that's still a Frickin Lot Of Money in any non-hyperinflated currency,
but it's the sort of restriction that banks don't like to hear of.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, guyren@gmail.com, laurenz.albe@cybertec.at
Subject: Re: Content for talk on Postgres Type System at PostgresConf
In-Reply-To: <2758726.1709247071@sss.pgh.pa.us>
References: <F9A4875C-503C-4D1F-8AA6-0C204F60D3F4@gmail.com> <08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at> <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> <CAKFQuwbOXtDcYMM5SMbUNoxOSLCEjEgLG7qwnG2h15Vs51X69Q@mail.gmail.com> <2758726.1709247071@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox