From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
cc: ammmkilo@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18560: Inconsistent Behavior of PostgreSQL 'LIKE' Operator
In-reply-to: <ZqoIBlVvEsntf9Eu@depesz.com>
References: <18560-c6c21c3b8d5c3f9b@postgresql.org>
 <ZqoIBlVvEsntf9Eu@depesz.com>
Comments: In-reply-to hubert depesz lubaczewski <depesz@depesz.com>
	message dated "Wed, 31 Jul 2024 11:46:46 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3378619.1722436506.1@sss.pgh.pa.us>
Date: Wed, 31 Jul 2024 10:35:06 -0400
Message-ID: <3378620.1722436506@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3378620.1722436506%40sss.pgh.pa.us>
Precedence: bulk

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Wed, Jul 31, 2024 at 04:47:23AM +0000, PG Bug reporting form wrote:
>> CREATE TABLE t0(c0 INT NOT NULL, c1 CHAR(1) UNIQUE);

> I bet the thing is simply one more edge case that proves that one
> shouldn't be using char(n) datatype:
> https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29

Yeah, exactly.  Some surprising choices were made way-back-when about
which char(N) operations ought to ignore trailing blanks and which
shouldn't.  In the case at hand, a closer look shows the problem:

=# explain verbose select c1,(c1 like c1) from t0;
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on public.t0  (cost=0.00..43.00 rows=2200 width=6)
   Output: c1, (c1 ~~ (c1)::text)

There is a "char ~~ text" operator but no "char ~~ char" operator,
so the parser coerces the right-hand "c1" to text --- which causes
stripping of its trailing blank --- and then applies ~~, which
treats the trailing blank in its left-hand argument as significant.

Yes, this is arguably inconsistent, but so are a lot of other char(N)
behaviors.  Proposals to change anything about it have generally
failed on the grounds that (1) it's not very clear what would work
better and not just move the surprises around; (2) we risk breaking
applications that are expecting the current behaviors; and
(3) char(N) is a deprecated backwater that we shouldn't be putting
any effort into.

			regards, tom lane