From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: scott.marlowe <scott.marlowe@ihs.com>
Cc: elein <elein@varlena.com>
Cc: news.postgresql.org <jlim@natsoft.com.my>
Cc: pgsql-sql@postgresql.org
Subject: Re: CHAR(n) always trims trailing spaces in 7.4
Date: Wed, 18 Feb 2004 10:56:00 -0500
Message-ID: <23815.1077119760@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dev@archonet.com, scott.marlowe@ihs.com, elein@varlena.com, jlim@natsoft.com.my
Subject: Re: CHAR(n) always trims trailing spaces in 7.4
In-Reply-To: <23815.1077119760@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox