From n.bulatovic@zim.mpg.de Tue Feb  25 14:20:47 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 8832C4758BD
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 09:20:47 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 18808-09 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 09:20:36 -0500 (EST)
Received: from post.rzg.mpg.de (post.rzg.mpg.de [130.183.9.21])
	by postgresql.org (Postfix) with ESMTP id 51B76474E42
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 09:20:25 -0500 (EST)
Received: from zim03vm (zim03vm.ipp.mpg.de [130.183.2.227])
	by post.rzg.mpg.de (8.9.2/8.9.2) with SMTP id PAA93574
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 15:20:26 +0100
X-Envelope-To: <pgsql-sql@postgresql.org>
Message-ID: <002e01c2dcd9$b887b2b0$e302b782@ipp.mpg.de>
From: "Natasa Bulatovic" <n.bulatovic@zim.mpg.de>
To: <pgsql-sql@postgresql.org>
Subject: Any limitation in size for return result from SELECT?
Date: Tue, 25 Feb 2003 15:25:17 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002B_01C2DCE2.1A12E240"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/26
X-Sequence-Number: 12066

This is a multi-part message in MIME format.

------=_NextPart_000_002B_01C2DCE2.1A12E240
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

one question

Select statement returns as a result concatenated all varchar, text and cha=
r 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 resul=
t 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...J=
ust returns a row with a null result....
Any quick suggestions would be very useful....

best regards,
Bulatovic Natasa


------=_NextPart_000_002B_01C2DCE2.1A12E240
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>one question</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Select statement returns as a result conca=
tenated=20
all varchar, text and char columns of a table as one single column=20
...</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select col1||col2||col3||.....||col100 fro=
m=20
table</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>However, when the number of concatenated c=
olumns is=20
bigger than 22 no result is given back (tested in psql, as well as from=20
pgplsql)....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Most of these columns for the test data ar=
e=20
NULL...Datatypes of columns are mixed text, varchar, char...Select statemen=
t=20
does not return any error...Just returns a row with a null=20
result....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Any quick suggestions would be very=20
useful....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>best regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Bulatovic Natasa</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_002B_01C2DCE2.1A12E240--




From sszabo@megazone23.bigpanda.com Sun Mar  2 05:47:50 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 197DD4764F5
	for <pgsql-sql@postgresql.org>; Sun,  2 Mar 2003 00:47:50 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 68908-07 for <pgsql-sql@postgresql.org>;
	Sun,  2 Mar 2003 00:47:49 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 4179147E789
	for <pgsql-sql@postgresql.org>; Sat,  1 Mar 2003 22:03:00 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 3A5C0D617; Sat,  1 Mar 2003 19:03:03 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 25E5E5C04; Sat,  1 Mar 2003 19:03:03 -0800 (PST)
Date: Sat, 1 Mar 2003 19:03:03 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Natasa Bulatovic <n.bulatovic@zim.mpg.de>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Any limitation in size for return result from SELECT?
In-Reply-To: <002e01c2dcd9$b887b2b0$e302b782@ipp.mpg.de>
Message-ID: <20030301190150.E21955-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/39
X-Sequence-Number: 12079


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.





From mail@joeconway.com Sun Mar  2 07:54:58 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 2F72647642D
	for <pgsql-sql@postgresql.org>; Sun,  2 Mar 2003 02:54:58 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 77777-07 for <pgsql-sql@postgresql.org>;
	Sun,  2 Mar 2003 02:54:52 -0500 (EST)
Received: from joeconway.com (63-210-180-150.skyriver.net [63.210.180.150])
	by postgresql.org (Postfix) with ESMTP id 03334476500
	for <pgsql-sql@postgresql.org>; Sat,  1 Mar 2003 22:44:01 -0500 (EST)
Received: from [192.168.5.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.0.4)
	with ESMTP-TLS id 1625186; Sat, 01 Mar 2003 20:19:31 -0800
Message-ID: <3E617D7F.1010600@joeconway.com>
Date: Sat, 01 Mar 2003 19:41:51 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Natasa Bulatovic <n.bulatovic@zim.mpg.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Any limitation in size for return result from SELECT?
References: <002e01c2dcd9$b887b2b0$e302b782@ipp.mpg.de>
In-Reply-To: <002e01c2dcd9$b887b2b0$e302b782@ipp.mpg.de>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/41
X-Sequence-Number: 12081

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




From bruno@wolff.to Sun Mar  2 03:40:40 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E108A475B99
	for <pgsql-sql@postgresql.org>; Sat,  1 Mar 2003 22:40:40 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 80100-08 for <pgsql-sql@postgresql.org>;
	Sat,  1 Mar 2003 22:40:39 -0500 (EST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id BB8F64880B4
	for <pgsql-sql@postgresql.org>; Sat,  1 Mar 2003 22:30:47 -0500 (EST)
Received: (qmail 17917 invoked by uid 500); 2 Mar 2003 03:44:36 -0000
Date: Sat, 1 Mar 2003 21:44:36 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Natasa Bulatovic <n.bulatovic@zim.mpg.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Any limitation in size for return result from SELECT?
Message-ID: <20030302034436.GA17897@wolff.to>
Mail-Followup-To: Natasa Bulatovic <n.bulatovic@zim.mpg.de>,
	pgsql-sql@postgresql.org
References: <002e01c2dcd9$b887b2b0$e302b782@ipp.mpg.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002e01c2dcd9$b887b2b0$e302b782@ipp.mpg.de>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/37
X-Sequence-Number: 12077

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.