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