To: Richard Huxton <dev@archonet.com>
Cc: "scott.marlowe" <scott.marlowe@ihs.com>, elein <elein@varlena.com>,
	"news.postgresql.org" <jlim@natsoft.com.my>, pgsql-sql@postgresql.org
Subject: Re: CHAR(n) always trims trailing spaces in 7.4 
In-reply-to: <200402180940.16896.dev@archonet.com> 
References: <Pine.LNX.4.33.0402171706530.31955-100000@css120.ihs.com>
	<19236.1077063947@sss.pgh.pa.us>
	<200402180940.16896.dev@archonet.com>
Comments: In-reply-to Richard Huxton <dev@archonet.com>
	message dated "Wed, 18 Feb 2004 09:40:16 +0000"
Date: Wed, 18 Feb 2004 10:56:00 -0500
Message-ID: <23815.1077119760@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Richard Huxton <dev@archonet.com> writes:
> I've never really understood the rationale behind char(n) in SQL databases 
> (other than as backward compatibility with some old mainframe DB). 

There are (or were) systems in which the benefit of using fixed-width
columns is a lot higher than it is in Postgres.  The spec is evidently
trying to cater to them.  Too bad the writers whacked the semantics
around so cruelly to do it :-(

> The only sensible definition of char(n) that I can see would be:
> A text value of type char(n) is always "n" characters in length.

If the SQL spec were willing to leave it at that, I'd be happy.  But
we've got this problem that the trailing spaces are supposed to be
insignificant in at least some contexts.  I find the pre-7.4 behavior
to be pretty inconsistent.  For example, 7.3 and 7.4 agree on this:

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

Now given the above, wouldn't it stand to reason that

regression=# select ('foo   '::char(6) || 'bar') = ('foo' || 'bar');
 ?column?
----------
 f
(1 row)

or how about

regression=# select ('bar' || 'foo   '::char(6)) = ('bar' || 'foo');
 ?column?
----------
 f
(1 row)

In 7.4 both of these do yield true.  A closely related example is

regression=# select ('foo   '::char(6)) = ('foo'::text);

which yields false in 7.3 and true in 7.4.

I don't object to revisiting the behavior again, but 7.3 was not so
ideal that I want to just go back to it.

			regards, tom lane