Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfpFF-002Xqy-7U for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 22:51:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rfpFD-009HYI-O3 for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 22:51:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfpFD-009HY9-DI for pgsql-general@lists.postgresql.org; Thu, 29 Feb 2024 22:51:15 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfpFB-0029td-Av for pgsql-general@postgresql.org; Thu, 29 Feb 2024 22:51:15 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 41TMpBjZ2758727; Thu, 29 Feb 2024 17:51:11 -0500 From: Tom Lane To: "David G. Johnston" cc: Guyren Howe , Postgres General , Laurenz Albe Subject: Re: Content for talk on Postgres Type System at PostgresConf In-reply-to: References: <08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at> <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> Comments: In-reply-to "David G. Johnston" message dated "Thu, 29 Feb 2024 15:05:50 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <2758725.1709247071.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Thu, 29 Feb 2024 17:51:11 -0500 Message-ID: <2758726.1709247071@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "David G. Johnston" writes: > On Thu, Feb 29, 2024 at 2:38 PM Guyren Howe 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