Re: [BUGS] 7.4: CHAR padding inconsistency
Re: [BUGS] 7.4: CHAR padding inconsistency
От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
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, Pennsylvania 19073
Re: [BUGS] 7.4: CHAR padding inconsistency
От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
Added to TODO: Make LENGTH() of CHAR() not count trailing spaces --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian 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, Pennsylvania 19073
Re: [BUGS] 7.4: CHAR padding inconsistency
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Bruce Momjian 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
Re: [BUGS] 7.4: CHAR padding inconsistency
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Troels Arvin 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
Re: [BUGS] 7.4: CHAR padding inconsistency
От:
Troels Arvin <troels@arvin.dk>
Дата:
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
