From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | c k <shreeseva(dot)learning(at)gmail(dot)com> |
Cc: | Philip Couling <phil(at)pedal(dot)me(dot)uk>, "david(dot)sahagian(at)emc(dot)com" <david(dot)sahagian(at)emc(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: || versus concat( ), diff behavior |
Date: | 2012-03-04 18:44:34 |
Message-ID: | CFAF2E4B-633A-4340-A58C-4DA9E1605E9C@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)learning(at)gmail(dot)com> 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(at)yahoo(dot)com> wrote:
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Philip Couling
> Sent: Friday, March 02, 2012 4:47 PM
> To: david(dot)sahagian(at)emc(dot)com
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] || versus concat( ), diff behavior
>
> On 02/03/12 20:58, david(dot)sahagian(at)emc(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Meyland Andersen | 2012-03-04 19:52:18 | Return unknown resultset from a function |
Previous Message | c k | 2012-03-04 18:26:40 | Re: || versus concat( ), diff behavior |