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
