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 ;
INFO: abc abc
INFO: abcabc
On Mar 2, 2012, at 12:58 PM, <[email protected]> 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 ;
> 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, [email protected] 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 ;
> 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: [email protected]
[mailto:[email protected]] On Behalf Of Philip Couling
Sent: Friday, March 02, 2012 4:47 PM
To: [email protected]
Cc: [email protected]
Subject: Re: [GENERAL] || versus concat( ), diff behavior
On 02/03/12 20:58, [email protected] 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 ;
> 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.
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 ]'
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 <[email protected]> wrote:
On 02/03/12 20:58, [email protected] 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.
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.
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 ]'
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 02/03/12 20:58, [email protected] 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.
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.
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 ]'
> 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
By continuing to browse this website, you agree to the use of cookies. Go to Privacy Policy.
