Re: Bug in concat operator for Char? -- More Info

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Wed, 21 Jul 2004, Tom Lane wrote:
>> Yes.  We've been moving more and more steadily towards the notion that
>> trailing spaces in char(n) values are insignificant noise.  If you think
>> that trailing spaces are significant, you shouldn't be using char(n)
>> to store them.

> Well, the problem here is that technically we're returning the wrong type.
> We should be returning a char(l1+l2) rather than a text for a char
> concatenate, but similarly to the recent complaint about numerics, we
> don't really have a fully proper way to do that and it seems non-trivial.

Well, it'd be trivial to implement a char || char yielding char
operator; it could just point to the existing textcat function and
you'd get what you want.  (It would come out as char(-1), ie unspecified
length, but I'm not buying into doing the kind of analysis it would take
to predict the length.)  The real question in my mind is whether that
would be more or less consistent with the behavior in other cases.
Food for thought: in 7.4,

regression=# select ('X   '::char) = ('X'::char);
 ?column?
----------
 t
(1 row)

regression=# select ('Y   '::char) = ('Y'::char);
 ?column?
----------
 t
(1 row)

regression=# select ('X   '::char || 'Y   '::char) = ('X'::char || 'Y'::char);
 ?column?
----------
 t
(1 row)

If we change || as is proposed in this thread, then the last case would
yield 'false', because the first concatenation would yield 'X  Y  '
which is not equal to 'XY' no matter what you think about trailing
spaces.  I find it a bit disturbing that the concatenation of equal
values would yield unequal values.

IMHO the bottom line here is that the SQL-spec behavior of type char(N)
is completely brain-dead.  Practically all of the questions in this area
would go away if people used varchar(N) or text to store their data.

            regards, tom lane