On Fri, May 6, 2016 at 8:58 AM, Kohei KaiGai <kai...@kaigai.gr.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
