Re: CHAR LIKE VARCHAR

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: CHAR LIKE VARCHAR
Дата
Msg-id 8350.1118156080@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: CHAR LIKE VARCHAR  (Michael Glaesemann <grzm@myrealbox.com>)
Список pgsql-general
Michael Glaesemann <grzm@myrealbox.com> 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Logging query plan for queries
Следующее
От: Jochem van Dieten
Дата:
Сообщение: Re: pg_dump 8.0.3 failing against PostgreSQL 7.3.2