X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 3DE6E9FA4A8 for ; Tue, 13 Jun 2006 00:19:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 12041-06 for ; Tue, 13 Jun 2006 00:19:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from weatherbeeta.com.au (mail.weatherbeeta.com.au [202.154.127.242]) by postgresql.org (Postfix) with SMTP id 32CE29FA10A for ; Tue, 13 Jun 2006 00:19:09 -0300 (ADT) Received: (qmail 23682 invoked from network); 13 Jun 2006 03:19:07 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;deaf1f5cd16de5a8ccd348dc1c4b7357; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 13 Jun 2006 03:19:07 -0000 Received: from IT-PHIL ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d52e60b; Tue, 13 Jun 2006 13:16:54 +1000 Reply-To: From: "Phillip Smith" To: "'Aaron Bono'" ,"'Tom Lane'" Cc: Subject: Re: COPY to table with array columns (Longish) Date: Tue, 13 Jun 2006 13:18:31 +1000 Organization: Weatherbeeta P/L Message-ID: <008801c68e98$0cb1a620$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0089_01C68EEB.DE5DB620" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Importance: Normal In-Reply-To: X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1150168747.306-23673-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.05/0.13 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/127 X-Sequence-Number: 25363 This is a multi-part message in MIME format. ------=_NextPart_000_0089_01C68EEB.DE5DB620 Content-Type: text/plain;charset="US-ASCII" Content-Transfer-Encoding: quoted-printable The export from the D3 system is written in PICK BASIC - similar to = 'normal' BASIC. =20 This currently exports Attribute (field) 1 to 9 of each SYS record with = a Primary Key starting with "Z" (']' is a wildcard in the SSELECT = statement) 153 EXECUTE 'SSELECT SYS WITH A0 "Z]"' CAPTURING JUNK 154 LOOP 155 READNEXT SYS.ID THEN 156 READ SYS.REC FROM SYS, SYS.ID ELSE 157 SYS.ID =3D 'XXXXXX' 158 END 159 * 160 OUTLINE =3D SYS.ID 161 FOR SYS.SUB =3D 1 TO 9 162 OUTLINE =3D OUTLINE:AM:SYS.REC 163 NEXT SYS.SUB 164 * 165 CONVERT CHAR(252) TO "" IN OUTLINE 166 CONVERT CHAR(92) TO "" IN OUTLINE 167 CONVERT CHAR(34) TO "" IN OUTLINE 168 OUTLINE =3D OCONV(OUTLINE, "MCU") 169 N=3D%FPUTS(OUTLINE:NL, (CHAR*)STREAM) 170 END ELSE 171 SYS.ID =3D 'XXXXXX' 172 END 173 490 NULL 174 UNTIL SYS.ID =3D 'XXXXXX' DO REPEAT =20 So you're suggesting creating a child table for each SYS record? Ie, a = table called "ZPRECMPL" etc? =20 Thanks for your input guys, Cheers, -p =20 -----Original Message----- From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of = Aaron Bono Sent: Tuesday, 13 June 2006 12:58 PM To: Tom Lane Cc: phillips@weatherbeeta.com.au; pgsql-sql@postgresql.org Subject: Re: [SQL] COPY to table with array columns (Longish) =20 I agree with Tom. Personally I cannot think of a time I would use an = array column over a child table. Maybe someone can enlighten me on when an = array column would be a good choice. What language are you using to do the export if I may ask?=20 -Aaron On 6/12/06, Tom Lane wrote: "Phillip Smith" writes: > The whole sys file is variable length records like this - they range = =3D > from 1 > to over 17,000 fields per record.=20 17000? I think you really need to rethink your schema. While you could theoretically drop 17000 elements into a PG array column, you wouldn't like the performance --- it'd be almost unsearchable for instance.=20 I'd think about two tables, one with a single row for each SYS record from the original, and one with one row for each detail item (the invoice numbers in this case). With suitable indexes and a foreign key=20 constraint, this will perform a lot better than an array-based translation. And no, in neither case will you be able to import that file without massaging it first. regards, tom lane=20 =20 *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ------=_NextPart_000_0089_01C68EEB.DE5DB620 Content-Type: text/html;charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

The export from the D3 system is = written in PICK BASIC – similar to ‘normal’ = BASIC.

 

This currently exports Attribute = (field) 1 to 9 of each SYS record with a Primary Key starting with “Z” = (‘]’ is a wildcard in the SSELECT statement)

153 EXECUTE 'SSELECT SYS WITH A0 "Z]"' CAPTURING = JUNK

154 LOOP

155    READNEXT SYS.ID THEN

156       READ SYS.REC FROM = SYS, SYS.ID ELSE

157          = SYS.ID =3D 'XXXXXX'

158       = END

159       *

160       OUTLINE =3D = SYS.ID

161       FOR SYS.SUB =3D 1 TO = 9

162          = OUTLINE =3D OUTLINE:AM:SYS.REC<SYS.SUB,1>

163       NEXT = SYS.SUB

164       *

165       CONVERT CHAR(252) TO "" IN OUTLINE

166       CONVERT CHAR(92) TO "" IN OUTLINE

167       CONVERT CHAR(34) TO "" IN OUTLINE

168       OUTLINE =3D = OCONV(OUTLINE, "MCU")

169       = N=3D%FPUTS(OUTLINE:NL, (CHAR*)STREAM)

170    END ELSE

171       SYS.ID =3D = 'XXXXXX'

172    END

173 490 NULL

174 UNTIL SYS.ID =3D 'XXXXXX' DO REPEAT

 

So you’re suggesting creating = a child table for each SYS record? Ie, a table called = “ZPRECMPL” etc?

 

Thanks for your input = guys,

Cheers,

-p

 

-----Original Message-----
From: = aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On = Behalf Of Aaron Bono
Sent: Tuesday, 13 June 2006 12:58 PM
To: Tom Lane
Cc: = phillips@weatherbeeta.com.au; pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY = to table with array columns (Longish)

 

I agree with Tom.  Personally I cannot think of a time I would use an = array column over a child table.  Maybe someone can enlighten me on when = an array column would be a good choice.

What language are you using to do the export if I may ask?

-Aaron

On = 6/12/06, Tom Lane <tgl@sss.pgh.pa.us> = wrote:

"Phillip = Smith" <phillips@weatherbeeta.com.au= > writes:
> The whole sys file is variable length records like this - they = range =3D
> from 1
> to over 17,000 fields per record.

17000?  I think you really need to rethink your schema.  While you could
theoretically drop 17000 elements into a PG array column, you = wouldn't
like the performance --- it'd be almost unsearchable for instance.

I'd think about two tables, one with a single row for each SYS = record
from the original, and one with one row for each detail item (the
invoice numbers in this case).  With suitable indexes and a = foreign key
constraint, this will perform a lot better than an array-based
translation.

And no, in neither case will you be able to import that file without
massaging it first.

            &= nbsp;           re= gards, tom lane

 


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
------=_NextPart_000_0089_01C68EEB.DE5DB620--