From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: harry@harryclarke.me.uk,
        PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
Subject: Re: text and varchar are not equivalent
In-reply-to: 
 <CAKFQuwbUeWu8fp5J1FW2vBn8-5RGnVS+Jtdri38HJbYT+VLPmA@mail.gmail.com>
References: 
 <170747750218.3279716.17734031485916520031@wrigleys.postgresql.org>
 <CAKFQuwbUeWu8fp5J1FW2vBn8-5RGnVS+Jtdri38HJbYT+VLPmA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Fri, 09 Feb 2024 10:24:19 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1694197.1707501111.1@sss.pgh.pa.us>
Date: Fri, 09 Feb 2024 12:51:51 -0500
Message-ID: <1694198.1707501111@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1694198.1707501111%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Feb 9, 2024, 10:12 PG Doc comments form <noreply@postgresql.org>
> wrote:
>> The documentation implies that the data types text and varchar are
>> equivalent, but this is not the case with this test in Postgresql version
>> 16.

> Fair point.  But I'd rather further emphasize that char should just be
> avoided so this and other unexpected outcomes simply do not manifest in a
> real database scenario.  Rather than try and document how odd it's behavior
> is when dealing with intra-textual type conversions.

Yeah, this is less about varchar acting oddly and more about char
acting oddly.  The short answer though is that text is a preferred
type, varchar is not, and that makes a difference when resolving
whether to apply text's or char's equality operator.  You can
detect how it's being handled with EXPLAIN:

regression=# explain verbose SELECT vc = ch AS vc_ch FROM test;
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on pg_temp.test  (cost=0.00..17.88 rows=630 width=1)
   Output: ((vc)::bpchar = ch)
(2 rows)

regression=# explain verbose SELECT txt = ch AS txt_ch FROM test;
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on pg_temp.test  (cost=0.00..19.45 rows=630 width=1)
   Output: (txt = (ch)::text)
(2 rows)

			regards, tom lane