Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEWmF-0000lt-Sp for pgsql-bugs@arkaria.postgresql.org; Sun, 08 Jan 2023 14:35:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEWmC-0001WZ-NN for pgsql-bugs@arkaria.postgresql.org; Sun, 08 Jan 2023 14:35:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pESgs-00049K-Gb for pgsql-bugs@lists.postgresql.org; Sun, 08 Jan 2023 10:14:10 +0000 Received: from 101b.relay.hey.com ([204.62.115.196]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pESgl-0006z6-Pm for pgsql-bugs@lists.postgresql.org; Sun, 08 Jan 2023 10:14:09 +0000 Received: from hey.com (bigip-vip.rw-ash-int.37signals.com [10.20.0.24]) by 101.relay.hey.com (Postfix) with ESMTPS id 81EFDE0373; Sun, 8 Jan 2023 10:14:02 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hey.com; s=heymail; t=1673172842; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to; bh=PE2MCj3WP8v/o5EHwt/emmrgd1UDot1cn+7/2Svrgcg=; b=JvOSBMp0kNZNz+2j/MEz6F0uQb0mPqi20ScyQayAG1LuF5lrl2VQ1rCfPBrAqfTspHsB3b xHbfyWE9yd3xHdd+LMrrmo1lyww8faZNERDvJknDxsdPpinceGAJegNjnSa4Cs9/0GiBV2 62T4kNI8luyYn67Iko8YKXz3hrWS1JLsoNDyAe65055oS1j7qZJPg59UuwpXgqYJ9C6doy 0hK9KVAmWOavvBPkpxcp+sT+AKe7a7havFenF3hDprU2q5ER/4ZwQiJ6M7Ib6imf4EbLfU AO4axo1cl9xvcA4kBY/eT6jIqUiej5VLz6AlI/gqCN37tvCkPoyQhoZ7q6XjpQ== Date: Sun, 08 Jan 2023 11:13:57 +0100 From: Dino Maric To: Tom Lane Cc: pgsql-bugs@lists.postgresql.org Message-ID: <276a2c3825d677698e392f92d45893d1dc93d797@hey.com> In-Reply-To: <3452152.1673107986@sss.pgh.pa.us> Subject: Re: Confusion about the range types Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_63ba976a3c015_3a9a1db03741c"; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ----==_mimepart_63ba976a3c015_3a9a1db03741c Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks Tom, I understand. But still it does feel a bit strange that value I'm storing is different when presenting. For example I want to use range in healthcare app and value user stores it must be the same when presenting. So in that case it is [7-8] and [7-9) are not the same thing :) On January 7, 2023, Tom Lane wrote: > Dino Maric writes: > > When I insert range like this: > > INSERT INTO public.tests > > VALUES (int4range(7,8,'[]')) > > After when querying table my return value for this column is not > [7,8] > > but it is [7,9). > > I found this behaviour confusing, because I want to insert 7-8 > ranges > > (including upper value) and then present that range to a user. > > This is the effect of canonicalization, as explained here: > > https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES- > DISCRETE > > If you don't like it you can make a range type with a different > canonicalization function, or no such function, but that might > have odd effects on the behavior of range comparison operators. > > =C2=A0regards, tom lane -- Sent with HEY =E2=80=94 Email as it should be ----==_mimepart_63ba976a3c015_3a9a1db03741c Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thanks Tom, I understand.

But still it does feel a bit stra= nge that value I'm storing is different when presenting.
For example I= want to use range in healthcare app and value user stores it must be the= same when presenting.
So in that case it is [7-8] and [7-9) are not t= he same thing :)



On January 7, 2023, Tom Lane &= lt;tgl@sss.pgh.pa.us> wrote:
Dino Maric <dinom@hey= .com> writes:
> When I insert range like this:
> INSERT IN= TO public.tests
> VALUES (int4range(7,8,'[]'))
> After when q= uerying table my return value for this column is not [7,8]
> but it= is [7,9).
> I found this behaviour confusing, because I want to in= sert 7-8 ranges
> (including upper value) and then present that ran= ge to a user.

This is the effect of canonicalization, as explained= here:

https://www.postgresql.org/docs/current/rangetypes.html#RAN= GETYPES-DISCRETE

If you don't like it you can make a range type wi= th a different
canonicalization function, or no such function, but tha= t might
have odd effects on the behavior of range comparison operators= .

=C2=A0regards, tom lane

--
Sent with HEY =E2=80=94 Em= ail as it should be
----==_mimepart_63ba976a3c015_3a9a1db03741c--