Re: textlike under the LIKE operator for char(n)

On Fri, May 6, 2016 at 8:58 AM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:

> postgres=# select 'abcd'::char(20) LIKE 'ab%cd';
> ?column?
> ----------
> f
> (1 row)
>
> postgres=# select 'abcd'::char(4) LIKE 'ab%cd';
> ?column?
> ----------
> t
> (1 row)
>
> LIKE operator (that is eventually processed by textlike) considers the
> padding space of char(n) data type as a part of string.

The SQL standard generally requires this for CHAR(n) columns.

> On the other hands, equal operator ignores the padding space when it
> compares two strings.
>
> postgres=# select 'abcd'::char(20) = 'abcd';
> ?column?
> ----------
> t
> (1 row)
>
> postgres=# select 'abcd'::char(4) = 'abcd';
> ?column?
> ----------
> t
> (1 row)

The SQL standard specifically requires this exception to the
general rule.

> Is this behavior as expected? or, bug?

This has been discussed on community lists multiple times in the
past; you might want to search the archives. I'm not inclined to
dig through the standard for details on this point again right now,
but in general the behaviors we provide for CHAR(n) are mandated by
standard. It would not entirely shock me if there are some corner
cases where different behavior could be allowed or even more
correct, but my recollection is that what you have shown is all
required to work that way.

Generally, I recommend avoiding CHAR(n) columns like the plague.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company