Postgres Pro
Downloads
Home   >   mailing lists

Thread: length of string containing blanks is 0, || behaves differently than concat on string of blanks

length of string containing blanks is 0, || behaves differently than concat on string of blanks

From
"Ehrenreich, Sigrid"
Date:

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

Xing

 

HRB Kiel Nr. 3983

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

 

Consist – IT that works.

 

Attachment

Re: length of string containing blanks is 0, || behaves differently than concat on string of blanks

From
Tom Lane
Date:
"Ehrenreich, Sigrid" <Ehrenreich@consist.de> writes:
> I am not sure, if this is a bug. I think at least it is a documentation bug.

The char(n) datatype has some odd rules: generally, trailing blanks are
considered insignificant, which is why you're getting 0 for the length.
Trailing blanks will also get stripped whenever the value is converted
to varchar or text, which is what's happening in your || example.
On the other hand, concat() doesn't execute any SQL type conversions;
it just concatenates the I/O representations of the values, so that
the trailing blanks survive.

Yes, all of these things are documented somewhere.  The implications
aren't always obvious of course.

Almost always, the correct response to questions like this is "don't
use char(n)".  The preferred string type in Postgres is text, or if
you have a reason to have a specific character-length limit (pro tip:
you probably don't), then use varchar(n).  In any case, if you think
trailing blanks are valid data, char(n) is not what to store them in.

            regards, tom lane

RE: length of string containing blanks is 0, || behaves differentlythan concat on string of blanks

From
"Ehrenreich, Sigrid"
Date:
Hi Tom,

Thanks a lot for your explanation.
So, I'll consider this as feature 😉

Regards,
Sigrid

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.

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Friday, August 16, 2019 3:23 PM
To: Ehrenreich, Sigrid <Ehrenreich@consist.de>
Cc: pgsql-bugs@lists.postgresql.org; Hanke, Jan-Niklas <Hanke@consist.de>
Subject: Re: length of string containing blanks is 0, || behaves differently than concat on string of blanks

"Ehrenreich, Sigrid" <Ehrenreich@consist.de> writes:
> I am not sure, if this is a bug. I think at least it is a documentation bug.

The char(n) datatype has some odd rules: generally, trailing blanks are
considered insignificant, which is why you're getting 0 for the length.
Trailing blanks will also get stripped whenever the value is converted
to varchar or text, which is what's happening in your || example.
On the other hand, concat() doesn't execute any SQL type conversions;
it just concatenates the I/O representations of the values, so that
the trailing blanks survive.

Yes, all of these things are documented somewhere.  The implications
aren't always obvious of course.

Almost always, the correct response to questions like this is "don't
use char(n)".  The preferred string type in Postgres is text, or if
you have a reason to have a specific character-length limit (pro tip:
you probably don't), then use varchar(n).  In any case, if you think
trailing blanks are valid data, char(n) is not what to store them in.

            regards, tom lane