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 1rg5XF-00474E-VB for pgsql-general@arkaria.postgresql.org; Fri, 01 Mar 2024 16:14:59 +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 1rg5XE-00DzEQ-CI for pgsql-general@arkaria.postgresql.org; Fri, 01 Mar 2024 16:14:56 +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 1rg5XC-00DzEI-NM for pgsql-general@lists.postgresql.org; Fri, 01 Mar 2024 16:14:56 +0000 Received: from fout2-smtp.messagingengine.com ([103.168.172.145]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rg5X8-002Lkl-A6 for pgsql-general@postgresql.org; Fri, 01 Mar 2024 16:14:53 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailfout.nyi.internal (Postfix) with ESMTP id A73CD13800CB; Fri, 1 Mar 2024 11:14:49 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Fri, 01 Mar 2024 11:14:49 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding: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=1709309689; x=1709396089; bh=oE+glTEVx+aPidm74Q8HHTUSpkUHQZYRLNnxf/opuVM=; b= AONMKhJIA1Qpt03p6rLv6NvGW+uNZRQYCOlK2jpD2EyfcYpeERi79Q/mX1GeIctL SNNMGtCDmFZqii8j19gb0VLIsdbY/2o21kSKUeOHLUTErk404KKRTRDU/vDEVvvL lp820PcmSjIUll2B9mAlwTZu1kU20DV7LkgpYnpHOSXAXQsq2efryL1ECm+Bqy93 ydYvYZe0D9v2TrwyMvQmviO5Z9kaSFpbh51pBE6AlENpeYNukLQuN07RAZJqni0O yCdhz9hAUnxVHzHTHicxWVqxAvs/C+gbmacJhUgt4krAVGwr7gase9yNLmHfamT+ fHcuwtSkBUxSW6n5Fzv5rA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding: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=1709309689; x= 1709396089; bh=oE+glTEVx+aPidm74Q8HHTUSpkUHQZYRLNnxf/opuVM=; b=j 1OMo2NaeC9L7idu4ywcxbDUfhAhXL0W6FgRqXZ+V7RaJraBW6jTYM051Qr0a4MvX CQTVQmtk2e5nPfnOXSvp9Ubj3Oh4h3GDrClR475GPTmaF6ZOqN4qa3Dw+ZTtZaD5 xAKs/qvTBhqhTiZl5RQoKOkfl1P+OMHjI+p3ltiouH2o+bn8yeWLPy0raH0s8HGl ETBNqKx4x+GEiXFNNSN7vN822pU8fnUaaEVDxWvMTmFO+SUs4p2OYw/NTJpVKVEB 4d1a+NVqMFUc0PV1xfuVIw4O5LAII11u7mfLGPw1bsRjJ2QJGG7zbet1l9E7i47N t0q5o9AbbBphZD6Odi2dg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrhedugdekiecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfhfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepleegveekkeekueeigfdtveeileeuhfefudefteekjeff keejueejheegheegkedtnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghenuc evlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgr nhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 1 Mar 2024 11:14:48 -0500 (EST) Message-ID: Date: Fri, 1 Mar 2024 08:14:48 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Content for talk on Postgres Type System at PostgresConf Content-Language: en-US To: Laurenz Albe , Guyren Howe , Postgres General References: <08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at> <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/1/24 01:18, Laurenz Albe wrote: > On Thu, 2024-02-29 at 13:38 -0800, Guyren Howe wrote: >> what are the misconceptions, or where might I find them for  myself? > > In addition to what was already said: > >> 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? > > I find the semantics confusing: > > test=> SELECT 'a'::character(10); > bpchar > ════════════ > a > (1 row) > > Ok, it is 10 characters long. > > test=> SELECT length('a'::character(10)); > length > ════════ > 1 > (1 row) > > Or is it? https://www.postgresql.org/docs/current/datatype-character.html "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character. In collations where whitespace is significant, this behavior can produce unexpected results; for example SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even though C locale would consider a space to be greater than a newline. Trailing spaces are removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values, and when using pattern matching, that is LIKE and regular expressions." > > test=> SELECT 'a'::character(10) || 'b'::character(10); > ?column? > ══════════ > ab > (1 row) > > And why is the result not 20 characters long, with spaces between "a" and "b"? SELECT pg_typeof('a'::character(10) || 'b'::character(10)); pg_typeof ----------- text This is covered by "Trailing spaces are removed when converting a character value to one of the other string types.". Though that still leaves you with: SELECT pg_typeof(('a'::character(10) || 'b'::character(10))::char(20)); pg_typeof ----------- character SELECT ('a'::character(10) || 'b'::character(10))::char(20); bpchar ---------------------- ab > > Best avoid "character". > >>  * timestamptz is just converted to a timestamp in UTC. Folks might >> imagine that it stores the time zone but it doesn’t. > > Yes, and I find that lots of people are confused by that. > > You could talk about the interaction with the "timezone" parameter, and > that it is not so much a timestamp with time zone, but an "absolute timestamp", > and in combination with "timestamp" a great way to let the database handle > the difficult task of time zone conversion for you. > > Yours, > Laurenz Albe > > > -- Adrian Klaver adrian.klaver@aklaver.com