Thread: || versus concat( ), diff behavior

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

On Mar 2, 2012, at 12:58 PM, <david.sahagian@emc.com> 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

I'm not sure why || works differently than concat(), but char() is whitespace-padded. You told the db you wanted "10
characters",so that's what it gave you. Perhaps you wanted vchar(10)? For that matter, do you even want that
restrictionof 10 characters in the first place? Perhaps the type text is what you're really after? 

On 02/03/12 20:58, david.sahagian@emc.com 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

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

On 02/03/12 20:58, david.sahagian@emc.com 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

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@yahoo.com> wrote:

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

On 02/03/12 20:58, david.sahagian@emc.com 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

c k <shreeseva.learning@gmail.com> writes:
> what will be the reason for this?

The short answer is that || uses cast-to-text semantics, whereas concat
uses output-function semantics, and char(n) is one of the weird types
for which those are different.  Don't blame us, blame the SQL committee.
Or rather than blaming anybody, stop using char(n); it's a badly
defined data type, and there are almost no cases where varchar or text
isn't preferable.

            regards, tom lane