pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedtext and varchar are not equivalent
3+ messages / 3 participants
[nested] [flat]
* text and varchar are not equivalent
@ 2024-02-09 11:18 PG Doc comments form <noreply@postgresql.org>
2024-02-09 17:24 ` Re: text and varchar are not equivalent David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2024-02-09 11:18 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: harry@harryclarke.me.uk
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/datatype-character.html
Description:
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.
CREATE TEMPORARY TABLE test(ch char, vc varchar, txt text, txt0 text);
INSERT INTO test VALUES (' ', ' ', ' ','');
SELECT ch = vc AS ch_vc, ch = txt AS ch_txt, ch = txt0 AS ch_txt0,
vc = ch AS vc_ch, vc = txt AS vc_txt, vc = txt0 AS vc_txt0,
txt = ch AS txt_ch, txt = vc AS txt_vc, txt = txt0 AS txt_txt0,
txt0 = ch AS txt0_ch, txt0 = vc AS txt0_vc, txt0 = txt AS txt0_txt
FROM test;
ch_vc ch_txt ch_txt0 vc_ch vc_txt vc_txt0 txt_ch txt_vc txt_txt0 txt0_ch txt0_vc txt0_txt
TRUE FALSE TRUE TRUE TRUE FALSE FALSE TRUE FALSE TRUE FALSE FALSE
The tests are showing that the space character is treated differently in a
one character string. Whilst varchar = text, the comparison with char is
treated differently with text and varchar
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: text and varchar are not equivalent
2024-02-09 11:18 text and varchar are not equivalent PG Doc comments form <noreply@postgresql.org>
@ 2024-02-09 17:24 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-09 17:51 ` Re: text and varchar are not equivalent Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2024-02-09 17:24 UTC (permalink / raw)
To: harry@harryclarke.me.uk, PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
On Fri, Feb 9, 2024, 10:12 PG Doc comments form <noreply@postgresql.org>
wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/datatype-character.html
> Description:
>
> 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.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: text and varchar are not equivalent
2024-02-09 11:18 text and varchar are not equivalent PG Doc comments form <noreply@postgresql.org>
2024-02-09 17:24 ` Re: text and varchar are not equivalent David G. Johnston <david.g.johnston@gmail.com>
@ 2024-02-09 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-02-09 17:51 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: harry@harryclarke.me.uk, PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-02-09 17:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-09 11:18 text and varchar are not equivalent PG Doc comments form <noreply@postgresql.org>
2024-02-09 17:24 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-09 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox