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

<div dir="ltr">On Fri, May 6, 2016 at 8:58 AM, Kohei KaiGai <<a
href="mailto:kaigai@kaigai.gr.jp">kaigai@kaigai.gr.jp</a>>wrote:<br /><br />> postgres=# select 'abcd'::char(20)
LIKE'ab%cd';<br />>  ?column?<br />> ----------<br />>  f<br />> (1 row)<br />><br />> postgres=#
select'abcd'::char(4) LIKE 'ab%cd';<br />>  ?column?<br />> ----------<br />>  t<br />> (1 row)<br
/>><br/>> LIKE operator (that is eventually processed by textlike) considers the<br />> padding space of
char(n)data type as a part of string.<br /><br />The SQL standard generally requires this for CHAR(n) columns.<br /><br
/>>On the other hands, equal operator ignores the padding space when it<br />> compares two strings.<br />><br
/>>postgres=# select 'abcd'::char(20) = 'abcd';<br />>  ?column?<br />> ----------<br />>  t<br />> (1
row)<br/>><br />> postgres=# select 'abcd'::char(4) = 'abcd';<br />>  ?column?<br />> ----------<br />> 
t<br/>> (1 row)<br /><br />The SQL standard specifically requires this exception to the<br />general rule.<br /><br
/>>Is this behavior as expected? or, bug?<br /><br />This has been discussed on community lists multiple times in
the<br/>past; you might want to search the archives.  I'm not inclined to<br />dig through the standard for details on
thispoint again right now,<br />but in general the behaviors we provide for CHAR(n) are mandated by<br />standard.  It
wouldnot entirely shock me if there are some corner<br />cases where different behavior could be allowed or even
more<br/>correct, but my recollection is that what you have shown is all<br />required to work that way.<br /><br
/>Generally,I recommend avoiding CHAR(n) columns like the plague.<br /><br />--<br />Kevin Grittner<br />EDB: <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/>The Enterprise PostgreSQL Company<br /></div>