From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | harry(at)harryclarke(dot)me(dot)uk, PostgreSQL Documentation <pgsql-docs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: text and varchar are not equivalent |
Date: | 2024-02-09 17:51:51 |
Message-ID: | 1694198.1707501111@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Fri, Feb 9, 2024, 10:12 PG Doc comments form <noreply(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Doc comments form | 2024-02-10 00:08:00 | bgwriter_delay |
Previous Message | David G. Johnston | 2024-02-09 17:24:19 | Re: text and varchar are not equivalent |