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 1q9rLD-0008VD-3f for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jun 2023 18:05:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9rLB-0005zi-Nm for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jun 2023 18:05:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9rLB-0005ye-AB for pgsql-admin@lists.postgresql.org; Thu, 15 Jun 2023 18:05:01 +0000 Received: from mail-lj1-x236.google.com ([2a00:1450:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q9rL8-002ZuV-LG for pgsql-admin@lists.postgresql.org; Thu, 15 Jun 2023 18:05:00 +0000 Received: by mail-lj1-x236.google.com with SMTP id 38308e7fff4ca-2b41e700103so19660331fa.2 for ; Thu, 15 Jun 2023 11:04:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686852298; x=1689444298; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=W0QyTdTQR6LRzVvg7SjEc3QlCkWJcv/A5kzzfOysHZ8=; b=rkuDoB3+9b9hwaa9Y5J4a6uspuFYMkuP3WCtb9IjHYESzdCiL8msVVVgwIvP+Mzsr6 5sjyC7eWOCvz1qaUL8W2ah63Av3SwagnbGPQrL+4T8RFVJhK5PQrgsxLkJXzrDacxbV8 NHOPyxrL4vJVem3ZkyCyH+b5PtLXs7Y6FrvA3HHp4RnpQ2AQvHiOgu77UI18PAeaiXf+ oqmjB+AV/dwPF8/or5IHABEBnfek6Pm36lGhPzKn2qE7uyOX29r64cm79xQ9OkzH3PVq vCY6peetRIRmE1yZuc/9L5zyxfabhWqy+3EJTLstnSP99I5POins6sLkH1YPCEfbvrYX I24Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686852298; x=1689444298; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=W0QyTdTQR6LRzVvg7SjEc3QlCkWJcv/A5kzzfOysHZ8=; b=PTF6THf0Vd270d7sTz5STaArdax2l5c3IhmWuMW82gBiqKiCP5AZ28LATahAqH9Ryx K6MQ+cTouo/ze2Hw5aZV6wSH1UefwpE8sZ5TqpyPUipVq1yBxELcOnw0SbZM07iJMSv8 Mr/egyiHcwuemdwgxATETd6HbJ8fAWYrg+R5kl8hKfiiY0fybQ47RG15OSdfilMFxOXc UivICtQO57RODgwCdE0tdU5mz+KMO4JKwDWeWgeSeNyPxWlB6Y54dDuUfmDDi8sFYCrz rcLfi+h7d2HL+M2SeK4h7+90vrS8mxa/S0XAylQSLhEE2nA/zndVgCX2HJAzrWHUYhpR FvtQ== X-Gm-Message-State: AC+VfDxU8vJfzviMfJQptFV/r5l0sp6AzcD6yxJ6BGt0dyAEqQgy5TlB lKtmZI5WhpoQd4KnnienQnuTG/6asEXx83sZxyw= X-Google-Smtp-Source: ACHHUZ45pvOChNySjRv00uqJkPdeXYsfxwxLA2+cveQNCau9GdpL4CRGPbDheY6sBpqvJCuJgVSHmoIjxXSTUET3W9Q= X-Received: by 2002:a2e:80d8:0:b0:2b1:d5d0:f164 with SMTP id r24-20020a2e80d8000000b002b1d5d0f164mr158118ljg.13.1686852297388; Thu, 15 Jun 2023 11:04:57 -0700 (PDT) MIME-Version: 1.0 References: <2018059.1686782739@sss.pgh.pa.us> <0055148e2cc742cbbc23485ae0c6c04e@express-scripts.com> <2CAFD37D-68FC-4A01-BE65-FAF1ADF7A60A@elevated-dev.com> <1726236757.407336.1686838293703@mail.yahoo.com> <3f21240f59a54f96898886f2c9189cf3@express-scripts.com> In-Reply-To: From: "David G. Johnston" Date: Thu, 15 Jun 2023 11:04:41 -0700 Message-ID: Subject: Re: The same result for with SPACE and without SPACE To: M Sarwar Cc: Scott Ribe , "Wetmore, Matthew (CTR)" , mahesh , Mohammed Aslam , "pgsql-admin@lists.postgresql.org" , Tom Lane Content-Type: multipart/alternative; boundary="000000000000f9780a05fe2ee7bd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f9780a05fe2ee7bd Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jun 15, 2023 at 10:44=E2=80=AFAM M Sarwar = wrote: > Hello Scott and all, > > Here the question comes with respect to CHAR(10) to CHARACTeR VARYING( 10 > ) comparison results. > char_10 - type character ( 10 ) > var_char_10 - type character varying ( 10) > > When I do the comparison between char_10 and var_char_10 columns, I may > get the wrong results. > var_char_10 is always trimmed from right. > NO. varchar is never trimmed in this sense. Though casting to a varchar(n) will result in truncation of the input value to n characters - regardless of what those characters are. > char_10 has padded data of blank or spaces. > Now I compare char*10 and var_char_10 columns, I will get the wrong > results because char*_10 has padded spaces. > > Is that correct or will it ignore whitespaces at the end of char_10 colum= n? > > Testing shows that the varchar value gets promoted to char, not the other way around. postgres=3D# select '123 '::char(5) =3D '123 '::varchar; ?column? ---------- t (1 row) postgres=3D# select ('123 '::char(5))::varchar =3D '123 '::varchar; ?column? ---------- f (1 row) No one memorizes char behavior - if you must use it then test your code, and maybe be extra explicit with your casting too. David J. --000000000000f9780a05fe2ee7bd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Jun 15, 2023 at 10:44=E2=80=AFAM M Sarwar <sarwarmd02@outlook.com> wrote:=
Hello Scott and all,

Here the question comes with respect to CHAR(10) to CHARACTeR VARYING( 10 )= comparison results.
char_10=C2=A0 =C2=A0- type character ( 10 )
var_char_10 - type character varying ( 10)

When I do the comparison between char_10 and var_char_10 columns, I may get= the wrong results.
var_char_1= 0 is always trimmed from right.
<= br>
NO.=C2=A0 varchar is never trimmed in this sense.=C2=A0 Th= ough casting to a varchar(n) will result in truncation of the input value t= o n characters - regardless of what those characters are.
<= br>

char_10 ha= s padded data of blank or spaces.
Now I comp= are char10 and var_char_10 columns, I will get the wrong results because char_10 has padded spaces.

Is that correct or will it ignore whitespaces at the end of char_10 column?=


Testing= shows that the varchar value gets promoted to char, not the other way arou= nd.

postgres=3D# select '123 = =C2=A0'::char(5) =3D '123 =C2=A0 =C2=A0'::varchar;
=C2=A0?column?
----------
=C2=A0t
(1 row)

postgres=3D# select ('123 =C2=A0'::char(5))= ::varchar =3D '123 =C2=A0 =C2=A0'::varchar;
=C2=A0?colu= mn?
----------
=C2=A0f
(1 row)

No one memorizes char beh= avior - if you must use it then test your code, and maybe be extra explicit= with your casting too.

David J.

--000000000000f9780a05fe2ee7bd--