Re: [HACKERS] LIKE, CHAR(), and trailing spaces
On Wed, Feb 02, 2011 at 07:48:38PM -0500, Bruce Momjian wrote: Brendan Jurd wrote: On 3 February 2011 10:54, Bruce Momjian br...@momjian.us wrote: It seems LIKE is considering the trailing CHAR(10) field spaces as significant, even though our documentations says: -- snip -- It says trailing spaces are not significant for character comparisons --- the real question is whether LIKE is a comparison. ?Obvioiusly '=' is a comparison, but the system does not treat LIKE as a comparison in terms of trailing spaces. ?Is that desired behavior? Interesting. I would have to say that from the user point of view, LIKE is definitely a comparison, and if the rest of the operators on bpchar ignore whitespace then LIKE ought to as well. Is the situation the same for regex matches (~ operators)? Yes, I think so: test= SELECT 'a'::char(10) ~ 'a$'; ?column? -- f (1 row) test= SELECT 'a'::char(10) ~ 'a *$'; ?column? -- t (1 row) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com In my mind LIKE/~ are pattern matching operators and not a simple comparison operator. PostgreSQL is doing the right thing in restricting the somewhat bizarre treatment of trailing spaces to the '=' comparison function. I can only imagine what would be needed to allow exceptions to the pattern matching syntax to allow you to actually work with and match the trailing spaces otherwise. +10 for leaving the behavior as is. Regards, Ken + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LIKE, CHAR(), and trailing spaces
Kenneth Marshall k...@rice.edu writes: On Wed, Feb 02, 2011 at 07:48:38PM -0500, Bruce Momjian wrote: It seems LIKE is considering the trailing CHAR(10) field spaces as significant, even though our documentations says: +10 for leaving the behavior as is. Yeah, we've been around on this before if memory serves. I don't think there's a case for changing it that's strong enough to outweigh backwards-compatibility considerations. Also, anyone who does want the spaces to be stripped can just add an explicit cast to text first: char_variable::text LIKE ... If we change it then we'll have to provide some other weird notation to allow people to get at the old behavior (I suppose there are some people out there relying on it). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LIKE, CHAR(), and trailing spaces
On 3 February 2011 10:54, Bruce Momjian br...@momjian.us wrote: It seems LIKE is considering the trailing CHAR(10) field spaces as significant, even though our documentations says: -- snip -- It says trailing spaces are not significant for character comparisons --- the real question is whether LIKE is a comparison. Obvioiusly '=' is a comparison, but the system does not treat LIKE as a comparison in terms of trailing spaces. Is that desired behavior? Interesting. I would have to say that from the user point of view, LIKE is definitely a comparison, and if the rest of the operators on bpchar ignore whitespace then LIKE ought to as well. Is the situation the same for regex matches (~ operators)? Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LIKE, CHAR(), and trailing spaces
Brendan Jurd wrote: On 3 February 2011 10:54, Bruce Momjian br...@momjian.us wrote: It seems LIKE is considering the trailing CHAR(10) field spaces as significant, even though our documentations says: -- snip -- It says trailing spaces are not significant for character comparisons --- the real question is whether LIKE is a comparison. ?Obvioiusly '=' is a comparison, but the system does not treat LIKE as a comparison in terms of trailing spaces. ?Is that desired behavior? Interesting. I would have to say that from the user point of view, LIKE is definitely a comparison, and if the rest of the operators on bpchar ignore whitespace then LIKE ought to as well. Is the situation the same for regex matches (~ operators)? Yes, I think so: test= SELECT 'a'::char(10) ~ 'a$'; ?column? -- f (1 row) test= SELECT 'a'::char(10) ~ 'a *$'; ?column? -- t (1 row) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
