Troels Arvin wrote:
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
pgman (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Bruce said:
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

On Wed, 19 Nov 2003 14:50:24 -0500, Bruce Momjian wrote:

I certainly depends on their background. Personally, the padding
characteristics of the CHAR type was one of the first things about SQL
that I learned (the hard way). Oracle and DB2 people should be used to
PostgreSQL's old behaviour.

The CHAR type may seem strange to some, but they may then just
use VARCHAR.

I've started writing about it here:
http://troels.arvin.dk/db/rdbms/#data_types-char

Some of my test-material is also online:
http://troels.arvin.dk/db/tests/chartest-20031119a/

My summary:

With regard to CHAR-handling, PostgreSQL 7.4 is now in opposition to
- previous versions of PostgreSQL; bad enough on its own,
because there doesn't seem to have been a good discussion
about it first - I can only find a few messages about it [1]
- DB2
- Oracle
- MSSQL (which also behaves in a non-standard way,
but different from PostgreSQL 7.4)
7.4 is close to how MySQL works, though.

I'm sorry about not testing this before 7.4 went gold, but I believe that
this is a bug which should be corrected before too much confusion
is created.


Reference 1:
An interesting one is this one:
http://article.gmane.org/gmane.comp....h=char+padding

--
Greetings from Troels Arvin, Copenhagen, Denmark



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly