length of string containing blanks is 0, || behaves differently than concat on string of blanks - Mailing list pgsql-bugs

Hi,

I am not sure, if this is a bug. I think at least it is a documentation bug.

I would expect for concat and || to yield the same results, as long as no NULL is involved. But in my case when I concat a letter and a string of blanks, the results differ.

Is it a bug or is this a feature (then please point me to the documentation, because I could not find it)?

Steps to reproduce:

drop table blubb;

create table blubb (text1 character(3), text2 character(1));

insert into blubb (text1, text2) values ('   ', 'a');

commit;

prod5=> select concat('#',text1,'#'), length(text1), text2 from blubb;

concat | length | text2

--------+--------+-------

#   #  |      0 | a

(1 row)

The string contains three blanks. Its length should be 3 to my understanding.

prod5=> select * from blubb where concat(text1, text2) = '   a';

text1 | text2

-------+-------

       | a

(1 row)

This is what I expect.

prod5=> select * from blubb where text1||text2 = '   a';

text1 | text2

-------+-------

(0 rows)

Now, this comes unexpected. According to documentation || should concatenate both strings, giving the same result as concat above.

Regards,

Sigrid Ehrenreich

Dipl.-Informatikerin

Senior Consultant

Consist Software Solutions GmbH

A Consist World Group Company

Christianspries 4, 24159 Kiel, Germany

Telefon  +49 431 / 39 93 - 623

Telefax  +49 431 / 39 93 - 999

E-Mail    Ehrenreich@consist.de

Web     www.consist.de

HRB Kiel Nr. 3983

Geschäftsführer: Daniel Ries, Martin Lochte-Holtgreven, Jörg Hansen 

Consist – IT that works.