"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes: > On Thu, Feb 29, 2024 at 2:38 PM Guyren Howe <guyren@xxxxxxxxx> 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
