Search Postgresql Archives

Re: CHAR LIKE VARCHAR

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Michael Glaesemann <grzm@xxxxxxxxxxxxx> writes:
> On Jun 7, 2005, at 6:03 PM, Richard Huxton wrote:
>> If you ask me (and it's too late to back out now :-) the whole  
>> behaviour of CHAR(n) is wrong, broken and just a bad idea.

> Just a quick addition: AFAIK, this bad, broken behavior (I definitely  
> agree!) is per SQL spec.  :( Don't know about the LIKE operator,  
> however.

AFAICS the behavior of LIKE is per spec also.  I quote from SQL92:

            b) The <predicate>

                 M LIKE P

              is true if there exists a partitioning of M into substrings
              such that:

              i) A substring of M is a sequence of 0 or more contiguous
                 <character representation>s of M and each <character repre-
                 sentation> of M is part of exactly one substring.

             ii) If the i-th substring specifier of P is an arbitrary char-
                 acter specifier, the i-th substring of M is any single
                 <character representation>.

            iii) If the i-th substring specifier of P is an arbitrary string
                 specifier, then the i-th substring of M is any sequence of
                 0 or more <character representation>s.

             iv) If the i-th substring specifier of P is neither an arbi-
                 trary character specifier nor an arbitrary string speci-
                 fier, then the i-th substring of M is equal to that sub-
                 string specifier according to the collating sequence of
                 the <like predicate>, without the appending of <space>
                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                 characters to M, and has the same length as that substring
                 ^^^^^^^^^^^^^^^
                 specifier.

              v) The number of substrings of M is equal to the number of
                 substring specifiers of P.

Under ordinary CHAR rules we could consider '1 ' as the substring
matching the final '1' of the pattern, so that 's' 't' 'r' '1 '
would match the pattern 's' 't' 'r' '1', but the underlined part of
rule 8.5.5.b.iv appears to specifically forbid that for LIKE.

Which is too bad, because it would be easy to "fix" this to behave
more sanely --- just remove the bpcharlike function and associated ~~
operator, so that the case would be handled by converting CHAR(n)
to text (and thereby stripping trailing spaces).

The bottom line is indeed that CHAR() is a nasty, useless, misdesigned
datatype.  Use text or varchar.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux