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 1rfoQW-002SQe-7Q for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 21:58:53 +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 1rfoQU-008uMA-M7 for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 21:58:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfoQS-008uM2-G6 for pgsql-general@lists.postgresql.org; Thu, 29 Feb 2024 21:58:50 +0000 Received: from fout6-smtp.messagingengine.com ([103.168.172.149]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfoQO-002DkV-3N for pgsql-general@postgresql.org; Thu, 29 Feb 2024 21:58:47 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailfout.nyi.internal (Postfix) with ESMTP id DE9CD13800CD; Thu, 29 Feb 2024 16:58:43 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute7.internal (MEProxy); Thu, 29 Feb 2024 16:58:43 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1709243923; x=1709330323; bh=nSRC3TOwv3 Obaf4Qlsvc/pF1Clen2L7zvYb1D0oTv8M=; b=F+iOEhHT90jPf7P9hxpaVHUPdU T/kt04ZVyPydozEsa8lh5lFWAst52PXs8cN8E19VI8hzj/M7qWxM5R+VrENbhiYj 9zuhwbv2Ckh7yAsQ/RaoXcdk3XW+qKJ5iNrim2/wlljZi8GDVO9xzcMoKV3K00Na d62FngNCaPRR8C2WxamHCQcN/AD5ycwnuZcBeSjhBX4I5yJlMZ/zQXk/HrJS3LYJ CnLCp+mODWcoybNVT6G8vnrG7w/0lipW3w05VIvBnkn5JCPfmZSWFAztuaR1xPT9 YwJqChiwXEzTXk6TRgHJbvc5kOZ+saZFZx3N4te18J5yiLeUc3qEfA5y60wQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; t=1709243923; x=1709330323; bh=nSRC3TOwv3Obaf4Qlsvc/pF1Clen 2L7zvYb1D0oTv8M=; b=l047AWh4HH6Ky5nipv0V4qTKivEdauKL6+KSYM4F2vRT cG5XX8x9tpcqbUpUfxuU7e5LIDJpaKb7BVzK9R7cq4/G/LcDTdGhs3KDxJChExsk 12x9Zy6kX7g1IfTqxPs6hqEvYYEVxbuk5grc0G+D/w4KmO/W3HZykB9wBHB7Rs8R u2l8V/gjB1vese0KDCkKIZQSUGvTjHaq641iira5Slb9Za9Nl/+z+6p8AH9xVfgU cV9LDANvYtRh2OROckSVJBuJQ8UthiYXKIMwo5xGZQc2ZuGfzNrFua2ESUVHXCmY sH1dmjF2RfYfNLJko5hQWLEnLvpUn2Ta6t5EPK+vjQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrgeelgdduheegucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurheptgfkffggfgfhuffvfhgjsegrtderredtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepjeektddvjeetudeijeeutefhteeiuddtuddvveekheek hfetgefhleeggeejieetnecuffhomhgrihhnpehpohhsthhgrhgvshgtohhnfhdrohhrgh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughr ihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 29 Feb 2024 16:58:42 -0500 (EST) Content-Type: multipart/alternative; boundary="------------bAlfHa4H0TsxiP7zKE9wnGkF" Message-ID: <63cdee15-f11b-47cd-b023-d62aa8b8a8fa@aklaver.com> Date: Thu, 29 Feb 2024 13:58:41 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird From: Adrian Klaver Subject: Re: Content for talk on Postgres Type System at PostgresConf To: Guyren Howe , Postgres General , Laurenz Albe References: <08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at> <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> Content-Language: en-US In-Reply-To: <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------bAlfHa4H0TsxiP7zKE9wnGkF Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 2/29/24 1:38 PM, Guyren Howe 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? > * timestamptz is just converted to a timestamp in UTC. Folks might > imagine that it stores the time zone but it doesn’t. > The issue I see is people getting confused when it come back rotated to whatever TimeZone is set to, which can change from client to client. > > * 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) > * 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 > test=# show lc_monetary ;  lc_monetary -------------  en_US.UTF8 (1 row) test=# select 1000::money;    money -----------  $1,000.00 (1 row) test=# set lc_monetary = 'en_GB.utf8'; SET test=# select 1000::money;    money -----------  £1,000.00 > On 29 Feb 2024 at 01:11 -0800, Laurenz Albe > , wrote: >> On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote: >>> I am to talk about Postgres’s type system at PGConf: >>> >>> https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system >>> >>> I picked the issue because I think it’s poorly understood, greatly >>> under-discussed, and an excellent way to empower postgres users. >>> >>> What would you want to see in such a talk? >> >> It might be good to explain how "timestamp with time zone" works. >> That's often confusing for beginners, because it is different from >> other databases and arguably deviates from the SQL standard. >> >> Perhaps it would be good to warn people about using data types like >> "character", "time with time zone" and "money". >> >> Yours, >> Laurenz Albe -- Adrian Klaver adrian.klaver@aklaver.com --------------bAlfHa4H0TsxiP7zKE9wnGkF Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 2/29/24 1:38 PM, Guyren Howe 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?
  • timestamptz is just converted to a timestamp in UTC. Folks might imagine that it stores the time zone but it doesn’t.
The issue I see is people getting confused when it come back rotated to whatever TimeZone is set to, which can change from client to client.
  • 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)
  • 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


test=# show lc_monetary ;
 lc_monetary
-------------
 en_US.UTF8
(1 row)

test=# select 1000::money;
   money   
-----------
 $1,000.00
(1 row)

test=# set lc_monetary = 'en_GB.utf8';
SET
test=# select 1000::money;
   money   
-----------
 £1,000.00


On 29 Feb 2024 at 01:11 -0800, Laurenz Albe <laurenz.albe@cybertec.at>, wrote:
On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote:
I am to talk about Postgres’s type system at PGConf:

https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system

I picked the issue because I think it’s poorly understood, greatly
under-discussed, and an excellent way to empower postgres users.

What would you want to see in such a talk?

It might be good to explain how "timestamp with time zone" works.
That's often confusing for beginners, because it is different from
other databases and arguably deviates from the SQL standard.

Perhaps it would be good to warn people about using data types like
"character", "time with time zone" and "money".

Yours,
Laurenz Albe
-- 
Adrian Klaver
adrian.klaver@aklaver.com
--------------bAlfHa4H0TsxiP7zKE9wnGkF--