pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCHAR(n) always trims trailing spaces in 7.4
17+ messages / 7 participants
[nested] [flat]
* CHAR(n) always trims trailing spaces in 7.4
@ 2004-02-16 03:29 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: news.postgresql.org @ 2004-02-16 03:29 UTC (permalink / raw)
To: pgsql-sql
Hello,
I just discovered the following change to CHAR(n) (taken from varlena.com,
general bits, issue 62).
This will cause me serious heart-ache, back-ache and bug-ache if we upgrade
to 7.4. Is there any compatibility flag available to use pre-7.4 behaviour
for CHAR(n)?
Regards, John
==================
In 7.4, one of the changes was that the char(n) type now truncates trailing
spaces. This causes a problem for those of us using ::char(n) to create
fixed length fields. Creating fixed length field batch files are usually
required to interface with legacy systems.
In the example below, psql is called from a shell with tuples only (t) and
unaligned (A). The result of the selection creates a fixed width file.
One of the techniques used to create fixed length fields in 7.3 and earlier
was to cast the value to char(n). This along with the various to_char()
functions used to be able to create fixed length records. For example:
% psql -At > batch.out << END
select accountid::char(30),
to_char( transaction_amount, 'FM000.00'),
(lastname || ',' || firstname )::char(40),
bankid::char(15),
to_char( now(), 'YYYYMMDD');
END
In 7.4 this no longer works. The fields created with the cast are no longer
fixed length. Instead of using the cast to make fixed length fields, use
rpad() and lpad() to do the same thing.
rpad(string text, length integer [, fill text])
lpad(string text, length integer [, fill text])
The previous selection should now be written as follows.
% psql -At > batch.out << END
select rpad(accountid,30),
to_char( transaction_amount, 'FM000.00'),
rpad( (lastname || ',' || firstname ), 40),
rpad(bankid, 15),
to_char( now(), 'YYYYMMDD');
END
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
@ 2004-02-17 23:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2004-02-17 23:10 UTC (permalink / raw)
To: news.postgresql.org <jlim@natsoft.com.my>; +Cc: pgsql-sql@postgresql.org, elein <elein@varlena.com>
"news.postgresql.org" <jlim@natsoft.com.my> writes:
> I just discovered the following change to CHAR(n) (taken from varlena.com,
> general bits, issue 62).
The description you quote doesn't appear to have much of anything to do
with the actual behavior of 7.4.
7.4 will trim trailing spaces when converting char(n) to varchar or
text, but the example query does not do that. It just coerces query
output columns to char(n), and that works the same as it did before.
For instance
regression=# select 'zit'::char(77);
bpchar
-------------------------------------------------------------------------------
zit
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-17 23:17 ` elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: elein @ 2004-02-17 23:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql@postgresql.org, elein <elein@varlena.com>
This is an example of the problem. It used to expand
the middle thing to 15.
elein=# select 'x' || ' '::char(15) || 'x';
?column?
----------
xx
(1 row)
On Tue, Feb 17, 2004 at 06:10:56PM -0500, Tom Lane wrote:
> "news.postgresql.org" <jlim@natsoft.com.my> writes:
> > I just discovered the following change to CHAR(n) (taken from varlena.com,
> > general bits, issue 62).
>
> The description you quote doesn't appear to have much of anything to do
> with the actual behavior of 7.4.
>
> 7.4 will trim trailing spaces when converting char(n) to varchar or
> text, but the example query does not do that. It just coerces query
> output columns to char(n), and that works the same as it did before.
> For instance
>
> regression=# select 'zit'::char(77);
> bpchar
> -------------------------------------------------------------------------------
> zit
> (1 row)
>
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
@ 2004-02-17 23:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:56 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 2 replies; 17+ messages in thread
From: Tom Lane @ 2004-02-17 23:40 UTC (permalink / raw)
To: elein <elein@varlena.com>; +Cc: news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
elein <elein@varlena.com> writes:
> This is an example of the problem. It used to expand
> the middle thing to 15.
> elein=# select 'x' || ' '::char(15) || 'x';
> ?column?
> ----------
> xx
> (1 row)
Still does, but then the spaces go away again when the value goes into
the concatenation, because concatenation is a text operator.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-17 23:56 ` elein <elein@varlena.com>
1 sibling, 0 replies; 17+ messages in thread
From: elein @ 2004-02-17 23:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: elein <elein@varlena.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
So the problem is there. But blaming it on char was wrong.
It should be blamed on the varchar change. Hey, I thought
the truncation was for varchar and not text? It was for both?
It would be semantically tricky to change the operator.
The precendence is to convert to text. Now with
the implicit update of the char(n) to text for the operator
"corrupts" the char() value.
elein
On Tue, Feb 17, 2004 at 06:40:49PM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > This is an example of the problem. It used to expand
> > the middle thing to 15.
>
> > elein=# select 'x' || ' '::char(15) || 'x';
> > ?column?
> > ----------
> > xx
> > (1 row)
>
> Still does, but then the spaces go away again when the value goes into
> the concatenation, because concatenation is a text operator.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-18 00:07 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:23 ` Re: CHAR(n) always trims trailing spaces in 7.4 terry@ashtonwoodshomes.com
2004-02-18 00:25 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:27 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
1 sibling, 3 replies; 17+ messages in thread
From: scott.marlowe @ 2004-02-18 00:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: elein <elein@varlena.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
On Tue, 17 Feb 2004, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > This is an example of the problem. It used to expand
> > the middle thing to 15.
>
> > elein=# select 'x' || ' '::char(15) || 'x';
> > ?column?
> > ----------
> > xx
> > (1 row)
>
> Still does, but then the spaces go away again when the value goes into
> the concatenation, because concatenation is a text operator.
But then this:
select 'x'||' '||'x'
should produce xx, but it produces x x.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-18 00:23 ` terry@ashtonwoodshomes.com
2 siblings, 0 replies; 17+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-02-18 00:23 UTC (permalink / raw)
To: 'scott.marlowe' <scott.marlowe@ihs.com>; 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 'elein' <elein@varlena.com>; 'news.postgresql.org' <jlim@natsoft.com.my>; pgsql-sql
> select 'x'||' '||'x'
>
> should produce xx, but it produces x x.
>
INCORRECT
This
select 'x'||' '::char ||'x'
Should produce xx
This
select 'x'||' '||'x'
is restateable as select 'x'|| ' '::text ||'x'
And the || operand for text is not dropping the extra spaces hence correctly
x x
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of scott.marlowe
> Sent: Tuesday, February 17, 2004 7:07 PM
> To: Tom Lane
> Cc: elein; news.postgresql.org; pgsql-sql@postgresql.org
> Subject: Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
>
>
> On Tue, 17 Feb 2004, Tom Lane wrote:
>
> > elein <elein@varlena.com> writes:
> > > This is an example of the problem. It used to expand
> > > the middle thing to 15.
> >
> > > elein=# select 'x' || ' '::char(15) || 'x';
> > > ?column?
> > > ----------
> > > xx
> > > (1 row)
> >
> > Still does, but then the spaces go away again when the
> value goes into
> > the concatenation, because concatenation is a text operator.
>
> But then this:
>
> select 'x'||' '||'x'
>
> should produce xx, but it produces x x.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-18 00:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 09:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Richard Huxton <dev@archonet.com>
2 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2004-02-18 00:25 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: elein <elein@varlena.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> But then this:
> select 'x'||' '||'x'
> should produce xx, but it produces x x.
No, because the imputed type of those literals is text. You'd have to
cast the middle guy to char(n) explicitly to make its trailing spaces go
away when it's reconverted to text.
The real issue here is that trailing spaces in char(n) are semantically
insignificant according to the SQL spec. The spec is pretty vague about
which operations should actually honor that insignificance --- it's
clear that comparisons should, less clear about other things. I think
the 7.4 behavior is more consistent than what we had before, but I'm
willing to be persuaded to change it again if someone can give an
alternate definition that's more workable than this one.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:25 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-18 09:40 ` Richard Huxton <dev@archonet.com>
2004-02-18 15:56 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 18:06 ` Re: CHAR(n) always trims trailing spaces in 7.4 Jeremy Smith <jer@highboard.com>
0 siblings, 2 replies; 17+ messages in thread
From: Richard Huxton @ 2004-02-18 09:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; scott.marlowe <scott.marlowe@ihs.com>; +Cc: elein <elein@varlena.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
On Wednesday 18 February 2004 00:25, Tom Lane wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > But then this:
> > select 'x'||' '||'x'
> > should produce xx, but it produces x x.
>
> No, because the imputed type of those literals is text. You'd have to
> cast the middle guy to char(n) explicitly to make its trailing spaces go
> away when it's reconverted to text.
>
> The real issue here is that trailing spaces in char(n) are semantically
> insignificant according to the SQL spec. The spec is pretty vague about
> which operations should actually honor that insignificance --- it's
> clear that comparisons should, less clear about other things. I think
> the 7.4 behavior is more consistent than what we had before, but I'm
> willing to be persuaded to change it again if someone can give an
> alternate definition that's more workable than this one.
[rant on]
I've never really understood the rationale behind char(n) in SQL databases
(other than as backward compatibility with some old mainframe DB).
Insignificant spaces? If it's not significant, why is it there? You could
have a formatting rule that specifies left-aligned strings space-padded (as
printf) but that's not the same as mucking about appending and trimming
spaces.
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 you assign
less than "n" characters, it is right-padded with spaces. In all other
respects it behaves as any other text type of length "n" with right-trailing
spaces.
[rant off - ah, feel better for that :-]
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:25 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 09:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Richard Huxton <dev@archonet.com>
@ 2004-02-18 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2004-02-18 15:56 UTC (permalink / raw)
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
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:25 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 09:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Richard Huxton <dev@archonet.com>
@ 2004-02-18 18:06 ` Jeremy Smith <jer@highboard.com>
1 sibling, 0 replies; 17+ messages in thread
From: Jeremy Smith @ 2004-02-18 18:06 UTC (permalink / raw)
To: pgsql-sql
Also, to make char(n) even more annoying, I had the one character value "K"
stored in a column that was char(2). When I pulled it from the database and
tried to compare it to a variable with a value of "K" it came out inequal.
Of course in mysql, that was not a problem.
Jeremy
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Huxton
Sent: Wednesday, February 18, 2004 4:40 AM
To: Tom Lane; scott.marlowe
Cc: elein; news.postgresql.org; pgsql-sql@postgresql.org
Subject: Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
On Wednesday 18 February 2004 00:25, Tom Lane wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > But then this:
> > select 'x'||' '||'x'
> > should produce xx, but it produces x x.
>
> No, because the imputed type of those literals is text. You'd have to
> cast the middle guy to char(n) explicitly to make its trailing spaces go
> away when it's reconverted to text.
>
> The real issue here is that trailing spaces in char(n) are semantically
> insignificant according to the SQL spec. The spec is pretty vague about
> which operations should actually honor that insignificance --- it's
> clear that comparisons should, less clear about other things. I think
> the 7.4 behavior is more consistent than what we had before, but I'm
> willing to be persuaded to change it again if someone can give an
> alternate definition that's more workable than this one.
[rant on]
I've never really understood the rationale behind char(n) in SQL databases
(other than as backward compatibility with some old mainframe DB).
Insignificant spaces? If it's not significant, why is it there? You could
have a formatting rule that specifies left-aligned strings space-padded (as
printf) but that's not the same as mucking about appending and trimming
spaces.
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 you
assign
less than "n" characters, it is right-padded with spaces. In all other
respects it behaves as any other text type of length "n" with right-trailing
spaces.
[rant off - ah, feel better for that :-]
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-18 00:27 ` elein <elein@varlena.com>
2004-02-18 03:53 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 17+ messages in thread
From: elein @ 2004-02-18 00:27 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; elein <elein@varlena.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
Apparently the ::char is cast to varchar and then text?
That explains x || ' ' || x
On Tue, Feb 17, 2004 at 05:07:24PM -0700, scott.marlowe wrote:
> On Tue, 17 Feb 2004, Tom Lane wrote:
>
> > elein <elein@varlena.com> writes:
> > > This is an example of the problem. It used to expand
> > > the middle thing to 15.
> >
> > > elein=# select 'x' || ' '::char(15) || 'x';
> > > ?column?
> > > ----------
> > > xx
> > > (1 row)
> >
> > Still does, but then the spaces go away again when the value goes into
> > the concatenation, because concatenation is a text operator.
>
> But then this:
>
> select 'x'||' '||'x'
>
> should produce xx, but it produces x x.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:27 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
@ 2004-02-18 03:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 20:05 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2004-02-18 03:53 UTC (permalink / raw)
To: elein <elein@varlena.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
elein <elein@varlena.com> writes:
> Apparently the ::char is cast to varchar and then text?
No, directly to text, because the || operator is defined as taking text
inputs. But there's no practical difference between text and varchar on
this point.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:27 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-18 03:53 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-18 20:05 ` elein <elein@varlena.com>
2004-02-19 04:58 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: elein @ 2004-02-18 20:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: elein <elein@varlena.com>; scott.marlowe <scott.marlowe@ihs.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
So exactly what is the order of casts that produces
different results with:
'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'
Are operators being invoked both (text,text)?
I'm trying to understand the precedence that causes
the different results.
elein
On Tue, Feb 17, 2004 at 10:53:17PM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > Apparently the ::char is cast to varchar and then text?
>
> No, directly to text, because the || operator is defined as taking text
> inputs. But there's no practical difference between text and varchar on
> this point.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:27 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-18 03:53 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 20:05 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
@ 2004-02-19 04:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-19 18:55 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2004-02-19 04:58 UTC (permalink / raw)
To: elein <elein@varlena.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
elein <elein@varlena.com> writes:
> So exactly what is the order of casts that produces
> different results with:
> 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'
> Are operators being invoked both (text,text)?
The only relevant operator is "text || text" (there are also some ||
operators for arrays, bytea, and BIT, but these will all be discarded
as not the most plausible match). Therefore, in your first example the
unspecified literals will all be presumed to be text, so the space does
not get trimmed.
One of the things we could think about as a way to tweak the behavior is
creating "||" variants that are declared to accept char(n) on one or
both sides. These could actually use the same C implementation function
(textcat) of course. But declaring them that way would suppress the
invocation of rtrim() as char-to-text conversion.
However, if we did that then "||" would behave differently from other
operators on character strings, so it doesn't seem like a very
attractive option to me.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:27 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-18 03:53 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 20:05 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-19 04:58 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-19 18:55 ` elein <elein@varlena.com>
2004-02-19 20:06 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: elein @ 2004-02-19 18:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: elein <elein@varlena.com>; scott.marlowe <scott.marlowe@ihs.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
I guess I am asking about the cast sequence from
char(n) to text.
(' '::char(n))::text trims spaces. This is wrong, imo.
' '::text does not trim spaces.
' '::char(n) does not trim spaces and pads.
char(n) should not trim spaces, right? And
it doesn't on an insert. Text does not trim spaces.
Somewhere the space trimming occurs.
If it is in the operator || then the operator is wrong.
If char(n) is properly defined to not trim spaces then
there should be a separate cat for char(n). It is correct
for it to behave differently than cat for text and varchar
because of the different trimming behaviour.
I can do this patch if there is agreement. But
I may not be able to do it immediately.
elein
On Wed, Feb 18, 2004 at 11:58:37PM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > So exactly what is the order of casts that produces
> > different results with:
>
> > 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'
>
> > Are operators being invoked both (text,text)?
>
> The only relevant operator is "text || text" (there are also some ||
> operators for arrays, bytea, and BIT, but these will all be discarded
> as not the most plausible match). Therefore, in your first example the
> unspecified literals will all be presumed to be text, so the space does
> not get trimmed.
>
> One of the things we could think about as a way to tweak the behavior is
> creating "||" variants that are declared to accept char(n) on one or
> both sides. These could actually use the same C implementation function
> (textcat) of course. But declaring them that way would suppress the
> invocation of rtrim() as char-to-text conversion.
>
> However, if we did that then "||" would behave differently from other
> operators on character strings, so it doesn't seem like a very
> attractive option to me.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: CHAR(n) always trims trailing spaces in 7.4
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-17 23:40 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 00:07 ` Re: CHAR(n) always trims trailing spaces in 7.4 scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:27 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-18 03:53 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 20:05 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
2004-02-19 04:58 ` Re: CHAR(n) always trims trailing spaces in 7.4 Tom Lane <tgl@sss.pgh.pa.us>
2004-02-19 18:55 ` Re: CHAR(n) always trims trailing spaces in 7.4 elein <elein@varlena.com>
@ 2004-02-19 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2004-02-19 20:06 UTC (permalink / raw)
To: elein <elein@varlena.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; news.postgresql.org <jlim@natsoft.com.my>; pgsql-sql
elein <elein@varlena.com> writes:
> Somewhere the space trimming occurs.
The cast from char(n) to text (or varchar) is what's doing the trimming
in 7.4. I think you can mostly revert the change by changing that
pg_cast entry to specify no conversion function instead of rtrim().
However that would probably result in squirrely, non-spec behavior for
comparisons.
> If char(n) is properly defined to not trim spaces then
> there should be a separate cat for char(n).
Possibly, but I think that is considering the issue much too narrowly.
Concatenation is not the only textual operator.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2004-02-19 20:06 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-16 03:29 CHAR(n) always trims trailing spaces in 7.4 news.postgresql.org <jlim@natsoft.com.my>
2004-02-17 23:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:17 ` elein <elein@varlena.com>
2004-02-17 23:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 23:56 ` elein <elein@varlena.com>
2004-02-18 00:07 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 00:23 ` terry@ashtonwoodshomes.com
2004-02-18 00:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 09:40 ` Richard Huxton <dev@archonet.com>
2004-02-18 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 18:06 ` Jeremy Smith <jer@highboard.com>
2004-02-18 00:27 ` elein <elein@varlena.com>
2004-02-18 03:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 20:05 ` elein <elein@varlena.com>
2004-02-19 04:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-19 18:55 ` elein <elein@varlena.com>
2004-02-19 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
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