Postgres Pro
Downloads
Home   >   mailing lists

Thread: Concat truncates at 257 characters

Concat truncates at 257 characters

From
Patrick Lademan
Date:
Hello,

My automated database unit tests compare the actual result to the expected
result then provides a pass or fail status.  When it fails, it constructs a
difference string with an ^ pointing to each mismatching character between
the two.  Some of my tests have very long strings of data used to validate
that a function is working correctly under very specific test conditions.
 The problem is that the concat and || are truncating the result.

The following simple examples all truncate at 257 characters and add a
trailing " (...)".

select rpad('', 200, 'A') || rpad('', 200, 'B');

select rpad('', 200, 'A')::text || rpad('', 200, 'B')::text;

select cast( rpad('', 200, 'A')::text || rpad('', 200, 'B')::text as text );

select cast( rpad('', 200, 'A') as text ) || cast( rpad('', 200, 'B') as
text );

select cast( cast( rpad('', 200, 'A') as text ) || cast( rpad('', 200, 'B')
as text ) as text);

select concat( rpad('', 200, 'A'), rpad('', 200, 'B') );

select concat( rpad('', 200, 'A')::varchar(4000) , rpad('', 200,
'B')::varchar(4000)  )::varchar(4000);

select concat( rpad('', 200, 'A')::text , rpad('', 200, 'B')::text  )::text;

Database:
"PostgreSQL 9.3.1 on x86_64-apple-darwin, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
5658) (LLVM build 2336.9.00), 64-bit"

Mac:
  System Version: OS X 10.9 (13A603)
  Kernel Version: Darwin 13.0.0

How can I extend the concat beyond 257?

Thank you,

Pat

Re: Concat truncates at 257 characters

From
John R Pierce
Date:
On 11/25/2013 3:09 PM, Patrick Lademan wrote:
> The following simple examples all truncate at 257 characters and add a
> trailing " (...)".
>
> select rpad('', 200, 'A') || rpad('', 200, 'B');

hmmm?

$ psql scratch
....
scratch=# select rpad('', 200, 'A') || rpad('', 200, 'B');

?column?



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

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

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

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBB
(1 row)


not here.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: Concat truncates at 257 characters

From
David Johnston
Date:
More details please.


> The problem is that the concat and || are truncating the result.

No, that is not the case:

select length(cast( rpad('', 200, 'A')::text || rpad('', 200, 'B')::text as
text )) -- output -> 400

SELECT length(repeat('a', 50) || repeat('b', 200) || repeat('c', 100)) --
output -> 350



> My automated database unit tests compare the actual result to the expected
> result then provides a pass or fail status.

So some client-side interface is where the problem resides.  It is trying to
output more user-friendly content for extra-long strings.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Concat-truncates-at-257-characters-tp5780244p5780248.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

Re: Concat truncates at 257 characters

From
Patrick Lademan
Date:
What is difference between pgadmin and psql that would cause one to
truncate?
On Nov 25, 2013 6:18 PM, "John R Pierce" <pierce@hogranch.com> wrote:

> On 11/25/2013 3:09 PM, Patrick Lademan wrote:
>
>> The following simple examples all truncate at 257 characters and add a
>> trailing " (...)".
>>
>> select rpad('', 200, 'A') || rpad('', 200, 'B');
>>
>
> hmmm?
>
> $ psql scratch
> ....
> scratch=# select rpad('', 200, 'A') || rpad('', 200, 'B');
>
> ?column?
>
>
> ------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------------------
> ------
>  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> AAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
> BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
> BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
> BBBBB
> (1 row)
>
>
> not here.
>
> --
> john r pierce                                      37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Re: Concat truncates at 257 characters

From
John R Pierce
Date:
On 11/25/2013 3:53 PM, Patrick Lademan wrote:
> What is difference between pgadmin and psql that would cause one to
> truncate?

whats the difference between a GUI application thats putting data in
grid forms, vs a console application thats writing to stdout? there's
more differences than there are similarities.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: Concat truncates at 257 characters

From
David Johnston
Date:
Patrick Lademan wrote
> What is difference between pgadmin and psql that would cause one to
> truncate?
> On Nov 25, 2013 6:18 PM, "John R Pierce" <

> pierce@

> > wrote:
>
>> On 11/25/2013 3:09 PM, Patrick Lademan wrote:
>>
>>> The following simple examples all truncate at 257 characters and add a
>>> trailing " (...)".
>>>
>>> select rpad('', 200, 'A') || rpad('', 200, 'B');
>>>
>>
>> hmmm?
>>
>> $ psql scratch
>> ....
>> scratch=# select rpad('', 200, 'A') || rpad('', 200, 'B');
>>
>> ?column?
>>
>>
>> ------------------------------------------------------------
>> ------------------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------------------
>> ------
>>  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>> AAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
>> BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
>> BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
>> BBBBB
>> (1 row)
>>
>>
>> not here.

What usage pattern, specifically, are you implementing (not that it will
mean much to me personally since my use of both pgAdmin and psql is
minimal)?

pgAdmin's design premise is that the output it generates will be consumed by
a human during routine administrative tasks.  psql provides that level of
interaction plus the ability to configure its output to be processed
consistently by other software.

I was actually somewhat surprised that you could even use pgAdmin given the
level of automation you describe but maybe I assumed too much or pgAdmin can
do more than I imagined - though really psql was designed to meet your
automation needs pgAdmin mostly is not even if it has some conveniences.

That said there should be (ideally, not sure in reality) some way to disable
that behavior and force pgAdmin to output full text contents (w/o
human-friendly truncation).

David J.











--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Concat-truncates-at-257-characters-tp5780244p5780258.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

Re: Concat truncates at 257 characters

From
Patrick Lademan
Date:
This appears to be a new behavior in pgAdmin between versions 9.2 and 9.3
because I have existing unit tests with 516 character strings that I copy
and pasted from the pgAdmin result column.

How can I configure pgAdmin to display more than 257 characters in a result
column?



On Mon, Nov 25, 2013 at 7:10 PM, David Johnston <polobo@yahoo.com> wrote:

> Patrick Lademan wrote
> > What is difference between pgadmin and psql that would cause one to
> > truncate?
> > On Nov 25, 2013 6:18 PM, "John R Pierce" <
>
> > pierce@
>
> > > wrote:
> >
> >> On 11/25/2013 3:09 PM, Patrick Lademan wrote:
> >>
> >>> The following simple examples all truncate at 257 characters and add a
> >>> trailing " (...)".
> >>>
> >>> select rpad('', 200, 'A') || rpad('', 200, 'B');
> >>>
> >>
> >> hmmm?
> >>
> >> $ psql scratch
> >> ....
> >> scratch=# select rpad('', 200, 'A') || rpad('', 200, 'B');
> >>
> >> ?column?
> >>
> >>
> >> ------------------------------------------------------------
> >> ------------------------------------------------------------------------
> >> ------------------------------------------------------------
> >> ------------------------------------------------------------------------
> >> ------------------------------------------------------------
> >> ------------------------------------------------------------------------
> >> ------
> >>  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> AAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> BBBBB
> >> (1 row)
> >>
> >>
> >> not here.
>
> What usage pattern, specifically, are you implementing (not that it will
> mean much to me personally since my use of both pgAdmin and psql is
> minimal)?
>
> pgAdmin's design premise is that the output it generates will be consumed
> by
> a human during routine administrative tasks.  psql provides that level of
> interaction plus the ability to configure its output to be processed
> consistently by other software.
>
> I was actually somewhat surprised that you could even use pgAdmin given the
> level of automation you describe but maybe I assumed too much or pgAdmin
> can
> do more than I imagined - though really psql was designed to meet your
> automation needs pgAdmin mostly is not even if it has some conveniences.
>
> That said there should be (ideally, not sure in reality) some way to
> disable
> that behavior and force pgAdmin to output full text contents (w/o
> human-friendly truncation).
>
> David J.
>
>
>
>
>
>
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Concat-truncates-at-257-characters-tp5780244p5780258.html
> Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>