From: Shaun Watts <swatts@computer-systems.com>
To: Scott Marlowe <smarlowe@g2switchworks.com>
To: Chris Travers <chris@travelamericas.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Blank-padding
Date: Mon, 24 Oct 2005 10:04:39 -0500
Message-ID: <457C5811B5F0244B9CB13119E027F764040C05@CSISERV1.CSIIndy.local> (raw)
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
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: swatts@computer-systems.com, smarlowe@g2switchworks.com, chris@travelamericas.com, tgl@sss.pgh.pa.us, postgresql4@ultimeth.com
Subject: Re: Blank-padding
In-Reply-To: <457C5811B5F0244B9CB13119E027F764040C05@CSISERV1.CSIIndy.local>
* 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