[BUGS] 7.4: CHAR padding inconsistency
Hello,
I read about the padding-of-CHAR-values changes in the release notes for
7.4.
Making PostgreSQL less standard compliant is sad; I also disagree
with the statement that trimming of trailing white-space is what people
expect.
What's worse, and this may be classified as an error:
create table chartest(col char(10) not null);
insert into chartest values ('AAA');
select character_length(col) from chartest;
character_length
--
10
select character_length(col || 'hey') from chartest;
character_length
--
6
SELECT CHARACTER_LENGTH(col) <
CHARACTER_LENGTH(col||'hey') from chartest;
?column?
--
f
The last two results are horrifying, in my opinion, especially when you
consider them in concert: Concatenating a value with another value
decreases its length...
--
Greetings from Troels Arvin, Copenhagen, Denmark
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] 7.4: CHAR padding inconsistency
Troels Arvin wrote:
> Hello,
>
> I read about the padding-of-CHAR-values changes in the release notes for
> 7.4.
>
> Making PostgreSQL less standard compliant is sad; I also disagree
> with the statement that trimming of trailing white-space is what people
> expect.
>
> What's worse, and this may be classified as an error:
>
> create table chartest(col char(10) not null);
> insert into chartest values ('AAA');
>
> select character_length(col) from chartest;
> character_length
> --
>10
>
> select character_length(col || 'hey') from chartest;
> character_length
> --
> 6
>
> SELECT CHARACTER_LENGTH(col) <
> CHARACTER_LENGTH(col||'hey') from chartest;
> ?column?
> --
> f
>
> The last two results are horrifying, in my opinion, especially when you
> consider them in concert: Concatenating a value with another value
> decreases its length...
Horrifying?
Anyway, what did you want it to output? "AAA hey"? We could do
that, but I assume most people wouldn't expect that output? If you use
literals it does work:
test=> SELECT 'aaa ' || 'bb';
?column?
--
aaa bb
(1 row)
I tried this and it clipped too:
test=> SELECT CAST('aa ' AS CHAR(10)) || 'b';
?column?
--
aab
(1 row)
How do other databases handle this?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] 7.4: CHAR padding inconsistency
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';
aab
Oracle:
select cast('aa ' as char(10)) || 'b' from dual;
aab
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
