pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Blank-padding
5+ messages / 5 participants
[nested] [flat]
* Re: Blank-padding
@ 2005-10-22 05:39 Chris Travers <chris@travelamericas.com>
1 sibling, 1 reply; 5+ messages in thread
From: Chris Travers @ 2005-10-22 05:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>; pgsql-sql
Tom Lane wrote:
>"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.
>
I once built a telecom billing app where this might be important (fixed
length fields). Lets say you have fixed length fields defined as
char(n) datatypes. You may want to build a query to generate billing
records like:
select field1 || field2 || field3 || field4 || field5 ... AS bill_record
FROM lec_billing_entries;
It seels to me that I would expect trailing spaces to be preserved in
these cases. Having an implicit rtrim function is asking for problems.
Personally I would rather have to call rtrim explicitly than have the
backend treat the concatenation differently than if I do it on the client.
> 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.\
>
>
My only gripe here is that the implicit rtrimming is going to cause
problems in cases where you are trying to do things with fixed-length
fields, which is really where one is likely to use bpchar anyway. It is
not a showstopper, but I can see why some people don't like it. But
can't please everyone :-)
Best Wishes,
Chris Travers
Metatron Technology Consulting
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Blank-padding
@ 2005-10-22 15:16 Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dean Gibson (DB Administrator) @ 2005-10-22 15:16 UTC (permalink / raw)
To: pgsql-sql
On 2005-10-21 20:23, Tom Lane wrote:
> "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 (oops, trailing) 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
>
It's one thing, as with floating point values, where the internal
representation is identical (1.0 stored identically to 1.00), and
another thing where the internal representation is different (a la
strings storing 'abc ' differently from 'abc'. While programming
languages may differ as to how they compare those two strings, every one
that I'm familiar with does not strip trailing spaces when concatenating.
It's not an issue with me, but the 8.0 concatenation does violate the
"Principle of Least Astonishment". As does treating 'abc ' and 'abc'
different during comparisons (which 8.0 fixed).
So my tentative argument would be, strip trailing spaces on CHAR(n)
comparisons but not on concatenation or assignment, because you lost
information. You can always strip if you need to (as you do in other
languages).
As I said, this issue doesn't presently affect my databases, it's just
an observation.
-- Dean
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Blank-padding
@ 2005-10-24 14:32 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Chris Travers <chris@travelamericas.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2005-10-24 14:32 UTC (permalink / raw)
To: Chris Travers <chris@travelamericas.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>; pgsql-sql
On Sat, 2005-10-22 at 00:39, Chris Travers wrote:
> Tom Lane wrote:
>
> >"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.
> >
> I once built a telecom billing app where this might be important (fixed
> length fields). Lets say you have fixed length fields defined as
> char(n) datatypes. You may want to build a query to generate billing
> records like:
> select field1 || field2 || field3 || field4 || field5 ... AS bill_record
> FROM lec_billing_entries;
>
> It seels to me that I would expect trailing spaces to be preserved in
> these cases. Having an implicit rtrim function is asking for problems.
> Personally I would rather have to call rtrim explicitly than have the
> backend treat the concatenation differently than if I do it on the client.
If I rememberate correctificantly, this problem is or was caused by the
|| operator using the same internal logic for varchar / text AND char.
Tom, is it reasonable / doable to have || have a different set of
internal functions for each of those types.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Blank-padding
@ 2005-10-24 15:04 Shaun Watts <swatts@computer-systems.com>
0 siblings, 1 reply; 5+ messages in thread
From: Shaun Watts @ 2005-10-24 15:04 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; Chris Travers <chris@travelamericas.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>; pgsql-sql
I also have an issue with the blank padding at the end of my fields.
Is there any way to eliminate the blank padding at the end of character
fields in a table.
Such as you have field X as a char(6), but storing "abc" in it. Well
postgres seems to add the padding on the end of string that is being
stored.
So it is stored as "abc " instead of "abc". I don't want that padding
there.
I am fairly new to Postgres and have only dealt with Informix database
systems,
which don't store data this way.
Any help is very much appreciated.
Thanks,
Shaun
Shaun Watts
Programmer/Analyst
CSI - Computer Systems, Inc. Phone: 317.913.4160
12975 Parkside Drive Fax: 317.913.4175
Fishers, IN 46038 Toll Free: 800.860.1274
"To give anything less than your best is to sacrifice the gift." --
Steve Prefontaine
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Monday, October 24, 2005 9:46 AM
To: Chris Travers
Cc: Tom Lane; Dean Gibson (DB Administrator); pgsql-sql
Subject: Re: [SQL] Blank-padding
On Sat, 2005-10-22 at 00:39, Chris Travers wrote:
> Tom Lane wrote:
>
> >"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.
> >
> I once built a telecom billing app where this might be important
> (fixed length fields). Lets say you have fixed length fields defined
> as
> char(n) datatypes. You may want to build a query to generate billing
> records like:
> select field1 || field2 || field3 || field4 || field5 ... AS
> bill_record FROM lec_billing_entries;
>
> It seels to me that I would expect trailing spaces to be preserved in
> these cases. Having an implicit rtrim function is asking for
problems.
> Personally I would rather have to call rtrim explicitly than have the
> backend treat the concatenation differently than if I do it on the
client.
If I rememberate correctificantly, this problem is or was caused by the
|| operator using the same internal logic for varchar / text AND char.
Tom, is it reasonable / doable to have || have a different set of
internal functions for each of those types.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, 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] 5+ messages in thread
* Re: Blank-padding
@ 2005-10-24 15:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Shaun Watts <swatts@computer-systems.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-10-24 15:14 UTC (permalink / raw)
To: Shaun Watts <swatts@computer-systems.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Chris Travers <chris@travelamericas.com>; Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>; pgsql-sql
"Shaun Watts" <swatts@computer-systems.com> writes:
> Is there any way to eliminate the blank padding at the end of character
> fields in a table.
Use varchar, or text.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-24 15:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-22 05:39 ` Chris Travers <chris@travelamericas.com>
2005-10-24 14:32 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-22 15:16 ` Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>
2005-10-24 15:04 Re: Blank-padding Shaun Watts <swatts@computer-systems.com>
2005-10-24 15:14 ` 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