pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBlank-padding (was: Oracle buys Innobase)
2+ messages / 2 participants
[nested] [flat]
* Blank-padding (was: Oracle buys Innobase)
@ 2005-10-21 23:28 Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dean Gibson (DB Administrator) @ 2005-10-21 23:28 UTC (permalink / raw)
To: pgsql-sql
On 2005-10-21 09:47, Tom Lane wrote:
> Alex Turner <armtuk@gmail.com> writes:
>
>> It appears that casting to a char() causes spaces to be stripped (ignored) from the string:
>>
> mls=# select length('123 '::char(8));
> length
> --------
> 3
> (1 row)
>
>
>> I'm not sure about anyone else, but I would personaly consider that a bug?
>>
>
> No, it's a feature, as per extensive discussion some time ago when we made it do that. The general rule is that trailing spaces in a char(n) are semantically insignificant.
>
> regards, tom lane
>
I remember that discussion, and I was for the change. However, upon
doing some testing after reading the above, I wonder if the
blank-stripping isn't too aggressive. I have a CHAR(6) field (say,
named Z) that has "abc " in it. Suppose I want to append "x" to Z,
with any leading spaces in Z PRESERVED. The following do not work in 8.0.4:
select Z || 'x';
select Z::char(6) || 'x';
select Z::varchar(6) || 'x';
select (Z || ' ')::char(6) || 'x';
There are only two ways I've found:
select rpad( Z, 6) || 'x'; -- but "rpad" is apparently not a
SQL-standard function.
select cast (Z || ' ' as varchar(6)) || 'x'; -- hokey but
SQL-compliant
Is there something I'm missing???
-- Dean
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Blank-padding (was: Oracle buys Innobase)
@ 2005-10-22 03:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-10-22 03:23 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>; +Cc: pgsql-sql
"Dean Gibson (DB Administrator)" <postgresql4@ultimeth.com> writes:
> I remember that discussion, and I was for the change. However, upon
> doing some testing after reading the above, I wonder if the
> blank-stripping isn't too aggressive. I have a CHAR(6) field (say,
> named Z) that has "abc " in it. Suppose I want to append "x" to Z,
> with any leading spaces in Z PRESERVED.
(You meant trailing spaces, I assume.) Why exactly would you want to do
that? You decided by your choice of datatype that the trailing spaces
weren't significant. This gripe seems to me exactly comparable to
complaining if a numeric datatype doesn't remember how many trailing
zeroes you typed after the decimal point. Those zeroes aren't
semantically significant, so you have no case.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-22 03:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-21 23:28 Blank-padding (was: Oracle buys Innobase) Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>
2005-10-22 03:23 ` 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