pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAny limitation in size for return result from SELECT?
4+ messages / 4 participants
[nested] [flat]
* Any limitation in size for return result from SELECT?
@ 2003-02-25 14:25 Natasa Bulatovic <n.bulatovic@zim.mpg.de>
0 siblings, 3 replies; 4+ messages in thread
From: Natasa Bulatovic @ 2003-02-25 14:25 UTC (permalink / raw)
To: pgsql-sql
Hi,
one question
Select statement returns as a result concatenated all varchar, text and char columns of a table as one single column ...
select col1||col2||col3||.....||col100 from table
However, when the number of concatenated columns is bigger than 22 no result is given back (tested in psql, as well as from pgplsql)....
Most of these columns for the test data are NULL...Datatypes of columns are mixed text, varchar, char...Select statement does not return any error...Just returns a row with a null result....
Any quick suggestions would be very useful....
best regards,
Bulatovic Natasa
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Any limitation in size for return result from SELECT?
@ 2003-03-02 03:03 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Natasa Bulatovic <n.bulatovic@zim.mpg.de>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-03-02 03:03 UTC (permalink / raw)
To: Natasa Bulatovic <n.bulatovic@zim.mpg.de>; +Cc: pgsql-sql
On Tue, 25 Feb 2003, Natasa Bulatovic wrote:
> Select statement returns as a result concatenated all varchar, text
> and char columns of a table as one single column ...
>
> select col1||col2||col3||.....||col100 from table
>
> However, when the number of concatenated columns is bigger than 22 no
> result is given back (tested in psql, as well as from pgplsql)....
>
> Most of these columns for the test data are NULL...Datatypes of
> columns are mixed text, varchar, char...Select statement does not
> return any error...Just returns a row with a null result.... Any quick
> suggestions would be very useful....
Anything concatenated with a NULL is NULL. You'll probably want to use
COALESCE(col1,'')||... for each of the columns that can be null.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Any limitation in size for return result from SELECT?
@ 2003-03-02 03:41 Joe Conway <mail@joeconway.com>
parent: Natasa Bulatovic <n.bulatovic@zim.mpg.de>
2 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-03-02 03:41 UTC (permalink / raw)
To: Natasa Bulatovic <n.bulatovic@zim.mpg.de>; +Cc: pgsql-sql
Natasa Bulatovic wrote:
> select col1||col2||col3||.....||col100 from table
>
> However, when the number of concatenated columns is bigger than 22 no
> result is given back (tested in psql, as well as from pgplsql)....
>
> Most of these columns for the test data are NULL...Datatypes of
Based on your description, I'd guess column 23 is the first null one. If
you concatenate null with any value, you'll get a null result:
regression=# select 'hello';
?column?
----------
hello
(1 row)
regression=# select 'hello' || null;
?column?
----------
(1 row)
regression=# select ('hello' || null) is null;
?column?
----------
t
(1 row)
HTH,
Joe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Any limitation in size for return result from SELECT?
@ 2003-03-02 03:44 Bruno Wolff III <bruno@wolff.to>
parent: Natasa Bulatovic <n.bulatovic@zim.mpg.de>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2003-03-02 03:44 UTC (permalink / raw)
To: Natasa Bulatovic <n.bulatovic@zim.mpg.de>; +Cc: pgsql-sql
On Tue, Feb 25, 2003 at 15:25:17 +0100,
Natasa Bulatovic <n.bulatovic@zim.mpg.de> wrote:
>
> select col1||col2||col3||.....||col100 from table
>
> Most of these columns for the test data are NULL...Datatypes of columns are mixed text, varchar, char...Select statement does not return any error...Just returns a row with a null result....
If you concatenate null with another string you get null, not the other string.
You need to use coalesce to convert nulls to empty strings.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-02 03:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-25 14:25 Any limitation in size for return result from SELECT? Natasa Bulatovic <n.bulatovic@zim.mpg.de>
2003-03-02 03:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-03-02 03:41 ` Joe Conway <mail@joeconway.com>
2003-03-02 03:44 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox