Thread: Re: [BUGS] 7.4: CHAR padding inconsistency
Well, that certainly is interesting. Oracle and MS-SQL preserve the
trailing space when concatenating. Does anyone remember the logic for
trimming space with ||?
---------------------------------------------------------------------------
William ZHANG wrote:
>
> Bruce said:
> > How do other databases handle this?
>
> I have tried on MS SQL Server 2000 and Oracle 9i for Windows.
> SQL Server doesn't like character_length and || , so use len and + instead.
> Oracle doesn't like character_length either, use length.
> Hope the result may help.
>
> create table chartest(col char(10) not null);
> insert into chartest values ('AAA');
>
> PostgreSQL:
> select character_length(col) from chartest;
> 10
> SQL Server
> select len(col) from chartest;
> 3
> Oracle
> select length(col) from chartest;
> 10
>
> PostgreSQL:
> select character_length(col || 'hey') from chartest;
> 6
> SQL Server:
> select len(col + 'hey') from chartest;
> 13
> Oracle:
> select length(col || 'hey') from chartest;
> 13
>
> PostgreSQL:
> select 'aaa ' || 'bb';
> aaa bb
> SQL Server:
> select 'aaa ' + 'bb';
> aaa bb
> Oracle:
> select 'aaa ' || 'bb' from dual;
> aaa bb
>
> PostgreSQL:
> select cast('aa ' as char(10)) || 'b';
> aab
> SQL Server:
> select cast('aa ' as char(10)) + 'b';
> aa b
> Oracle:
> select cast('aa ' as char(10)) || 'b' from dual;
> aa b
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Well, that certainly is interesting. Oracle and MS-SQL preserve the
> trailing space when concatenating. Does anyone remember the logic for
> trimming space with ||?
"trimming space with ||" is a completely inaccurate description of
what's happening.
7.4 trims spaces from char(n) data when converting it to text (or
varchar). Since we don't have a bpchar version of ||, only a text
version, the implicitly invoked conversion is what's making the
difference.
AFAICS the Oracle and SQL Server behaviors are at least as inconsistent
as our own. If trailing spaces are significant during concatenation,
why aren't they significant to LENGTH()? I can't see a justification
in the SQL spec for handling one case differently from the other.
Actually the SQL spec is pretty inconsistent itself. It's clear that
trailing spaces are insignificant in comparisons, if you are using a
PAD SPACE collation which I think is the implication of CHAR(n), but
I don't see anything that says that they are insignificant for other
purposes such as LENGTH() and concatenation.
I'd agree with changing bpcharlen() to not count trailing spaces,
I think. That would be consistent with ignoring them in other contexts.
regards, tom lane
On Thu, 20 Nov 2003 12:40:30 -0500, Tom Lane wrote: > AFAICS the Oracle and SQL Server behaviors are at least as inconsistent > as our own. > If trailing spaces are significant during concatenation, > why aren't they significant to LENGTH()? Oracle _does_ count the trailing spaces in it's LENGTH()-function. MSSQL's rules certainly look strange. > I'd agree with changing bpcharlen() to not count trailing spaces, > I think. That would be consistent with ignoring them in other contexts. Why not just change the behaviour back to what it used to be like? I see no justification for the change: It may break old queries in subtle ways and doesn't make the CHAR-type any more consistent than before. -- Greetings from Troels Arvin, Copenhagen, Denmark
Troels Arvin <troels@arvin.dk> writes:
> Why not just change the behaviour back to what it used to be like? I see
> no justification for the change:
Well, for one thing, it makes the behavior of comparisons compatible
with the SQL standard. If we have unpleasant side-effects we can work
on those, but I don't have a lot of patience for saying "revert it"
without offering a better solution.
regards, tom lane
Added to TODO: Make LENGTH() of CHAR() not count trailing spaces --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Well, that certainly is interesting. Oracle and MS-SQL preserve the > > trailing space when concatenating. Does anyone remember the logic for > > trimming space with ||? > > "trimming space with ||" is a completely inaccurate description of > what's happening. > > 7.4 trims spaces from char(n) data when converting it to text (or > varchar). Since we don't have a bpchar version of ||, only a text > version, the implicitly invoked conversion is what's making the > difference. > > AFAICS the Oracle and SQL Server behaviors are at least as inconsistent > as our own. If trailing spaces are significant during concatenation, > why aren't they significant to LENGTH()? I can't see a justification > in the SQL spec for handling one case differently from the other. > > Actually the SQL spec is pretty inconsistent itself. It's clear that > trailing spaces are insignificant in comparisons, if you are using a > PAD SPACE collation which I think is the implication of CHAR(n), but > I don't see anything that says that they are insignificant for other > purposes such as LENGTH() and concatenation. > > I'd agree with changing bpcharlen() to not count trailing spaces, > I think. That would be consistent with ignoring them in other contexts. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073