Re: || versus concat( ), diff behavior

[David Johnston <polobo@xxxxxxxxx>]

Both varchar and text keep the trailing whitespace since it must be significant. A char removes all trailing whitespace (with the || operator) since it cannot distinguish between padding supplied whitespace (insignificant) and user-supplied whitespace (significant).

On Mar 4, 2012, at 13:26, c k <shreeseva.learning@xxxxxxxxx> wrote:

I have found an another difference.

select 'AA   '::char(5) || 'AA'::char(5);

result: "AAAA"

while
select 'AA   '::varchar(5) || 'AA'::varchar(5);
gives

result: "AA   AA"

select 'AA   '::text || 'AA'::text;
gives

result: "AA   AA"

what will be the reason for this?

Regards,

C P Kulkarni

On Sat, Mar 3, 2012 at 3:40 AM, David Johnston <polobo@xxxxxxxxx> wrote:

-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Philip Couling
Sent: Friday, March 02, 2012 4:47 PM
To: david.sahagian@xxxxxxx
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: || versus concat( ), diff behavior

On 02/03/12 20:58, david.sahagian@xxxxxxx wrote:
> Can anybody please point me to where this "difference of behavior" is
explained/documented ?
> Thanks,
> -dvs-
>
> -- version = 9.1.3
> do $$
> declare
>    v_str  char(10);
> begin
>    v_str := 'abc' ;
>    raise info '%', concat(v_str, v_str) ;
>    raise info '%', v_str||v_str ;
> end
> $$;
>
> INFO:  abc       abc
> INFO:  abcabc
>
>

Concat is a function which concatenates whatever you give it blindly.
Hence it has the behavior that includes the blanks.

The || operator reflects the more general PostgreSQL principle that trailing
blanks are insignificant for char fields.  You see the same behavior when
comparing char variables.


This can be found in the manual:

http://www.postgresql.org/docs/current/static/datatype-character.html

Values of type character are physically padded with spaces to the specified
width n, and are stored and displayed that way. However, the padding spaces
are treated as semantically insignificant. Trailing spaces are disregarded
when comparing two values of type character, and they will be removed when
converting a character value to one of the other string types. Note that
trailing spaces are semantically significant in character varying and text
values, and when using pattern matching, e.g. LIKE, regular expressions.


Hope this makes it just a little clearer.

Regards

----------------------------------------------------------------------------
-----

Philip,

The question to ask is whether the behavior of the "concat" function is
intentionally different than the "||" operator.  Aside from the ability to
take more than two arguments I would suggest they should behave identically.
Given the newness of the "concat" function I would guess the difference is
unintentional.  Regardless, either the documentation or the function code
needs to be modified: either to synchronize the behavior or to explicitly
point out the different treatment of "character" types.

I'd argue that the "||" behavior is incorrect but at this point it doesn't
matter.  Prior to the introduction of the "concat" function how would one
perform a concatenation with a "character" type and preserve the trailing
whitespace?  If the new function intends to fix that behavior documenting
such would be helpful.

DVS,

From a curiosity standpoint I presume that the "concat" output leaves
whitespace surrounding the second half as well?  In the future, when
debugging string content, I would suggest you bracket your output so you
know when there is trailing whitespace.  I.E., '[' || string_to_view || ']'
=> '[string with trailing whitespace      ]'

Dave

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general