Concatenating spaces into a string

[gargoyle47]

Having a real struggle concatenating spaces into a string.

I have a string (varchar) with a value of 'abcd'
I have a character (char) with a value of ' ' (space)

If I try to concatenate them like this...
select 'abcd' || ' ' || 'abcd';
then I was hoping to achieve 'abcd abcd', i.e. with a space between
the two strings.
This works fine in pgAdmin query window and also in psql native shell,
but within a bespoke function the space is dropped.

I assume that this is because during the concatenation using declared
variables of type varchar and char, the char is automatically cast as
varchar and the space is effectively dropped, or that the resulting
varchar result also has its concatenated trailing space truncated at
some point.

Either way I cannot find a way to get around this within a function.
I have tried different approaches, including using the overlay
function, but still no luck.
I have also tried declaring all function variables as char, no change.

Can anyone suggest a way around this within a function?

[Bob Badour]

gargoyle47 wrote:

Trailing spaces are insignificate in char types and significant in
varchar types. Try declaring your variables as varchar not char.

Compare:
select 'abcd'::char(9) || ' '::char(9) || 'abcd'::char(9)

with:
select 'abcd'::varchar(9) || ' '::varchar(9) || 'abcd'::varchar(9)

[gargoyle47]

On 23 May, 18:20, Bob Badour <bbad...@pei.sympatico.ca> wrote:
> Trailing spaces are insignificate in char types and significant in
> varchar types. Try declaring your variables as varchar not char.
>
> Compare:
> select 'abcd'::char(9) || ' '::char(9) || 'abcd'::char(9)
>
> with:

> select 'abcd'::varchar(9) || ' '::varchar(9) || 'abcd'::varchar(9)- Hide quoted text -
>

Thanks for that Bob.
For some reason in my mind I had the behaviour reversed. Even after
reading all the docs I still didn't see it.

Glad to know I'm not going crazy!

[Bob Badour]

gargoyle47 wrote:

I keep track of it by thinking "punch cards". Punch cards have fixed
width fields (char) and the trailing spaces don't count. For most of
everything else, lengths vary and spaces count.

[gargoyle47]

> everything else, lengths vary and spaces count.- Hide quoted text -
>
> - Show quoted text -

"punch cards" - ah, yes - I'm old enough to remember them ;-)

[Bob Badour]

gargoyle47 wrote:

> On 23 May, 22:57, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>
>>gargoyle47 wrote:
>>
>>>On 23 May, 18:20, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>>
>>>>Trailing spaces are insignificate in char types and significant in
>>>>varchar types. Try declaring your variables as varchar not char.
>>
>>>>Compare:
>>>>select 'abcd'::char(9) || ' '::char(9) || 'abcd'::char(9)
>>
>>>>with:
>>>>select 'abcd'::varchar(9) || ' '::varchar(9) || 'abcd'::varchar(9)- Hide quoted text -
>>
>>>Thanks for that Bob.
>>>For some reason in my mind I had the behaviour reversed. Even after
>>>reading all the docs I still didn't see it.
>>
>>>Glad to know I'm not going crazy!
>>
>>I keep track of it by thinking "punch cards". Punch cards have fixed
>>width fields (char) and the trailing spaces don't count. For most of
>>everything else, lengths vary and spaces count.
>

> "punch cards" - ah, yes - I'm old enough to remember them ;-)

I am just barely old enough to remember them. Actually, the closest I
ever came to using them were the ones you blacked out with a pencil
without actually punching holes.

Regardless, they have enough historical importance I would be surprised
if anyone using a dbms were unaware of them. And ultimately, they are
the origin of the behaviour of the char data type.