No problem!

SYS 'ZKCOST' size =3D 21

01 2750

This is a simple record in the SYS = file. Primary Key is “ZKCOST” and the Data would be 1 element with = a value of 2750.

SYS 'ZPRECMPL' size =3D 2069 &nb= sp; &nbs= p; = ; = O

01 928898

02 928899

03 928900

04 928901

05 928902

06 928903

07 928904

08 928907

09 928908

10 928909

11 928910

12 928915

13 928916

14 928917

15 928918

16 928919

17 928920

18 928921

19 928925

20 928926

21 928927

<snip>

Another SYS record – this is = a list of invoices that are waiting to be confirmed and therefore obviously = constantly change with additions and deletions. So in the SQL this would need to = have a Primary Key of “ZPRECMPL” and 21 elements in the array, each = with an invoice number… (well, there’s actually 293 in there at = the moment, but I don’t think I need to fill up the e-mail with all of = them!)

The whole sys file is variable = length records like this – they range from 1 to over 17,000 fields per = record.

Hope this helps,

Thanks,

-p

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

Can you provide an example?

Thanks,
Aaron

On = 6/11/06, Phillip Smith <phillips@weatherbeeta.com.au= > wrote:

Hi All,

Hope someone can help me – our main = company system runs on Raining Data PICK/D3 (if anyone familiar with it) which = stores records in it's "tables" as variable length items. Every item = has a unique Primary Key (per table) then each item can have a variable number = of fields. These fields are delimited by Char 254, then each field can have sub-values delimited by Char 253, then sub-sub-values delimited by Char = 252.

Anyway, we are trying to export everything to Postgres for reporting and querying etc (not to actually run the = system…. Yet) and hasn't been a problem so far – everything like stock and purchase orders, sales orders etc can pretty easily be turned in to a = flat file with standard number of columns and consistent data. We truncate every = table each night then import that latest TSV export from D3 using a COPY = command.

The problem arises with tables like our SYS = table which store generic system data, so one record could have 3 fields, but = the next could have 300. The only way I can work out how to export = multi-valued data like this to Postgres is to use an array column. So the table has 2 columns – the pkey and a data array.

How do I get this imported to the truncated = table each night? At the moment I think my best option is to modify the export = for the SYS table to call PSQL and use standard SQL INSERT statements to = directly insert it instead of exporting to a flat file, then import to = Postgres.

Thanks all,

-p

For those who are interested, or if it might = help, here's a rough comparison of the database structure of = D3:

Windows =3D PICK/D3 =3D = Postgres

Drive =3D Account =3D = Database

Directory =3D File = =3D Table

File =3D = Item =3D Row

Line in text file =3D Attribute = =3D Field

(none) =3D Value =3D = Array Element (?)

(none) =3D Sub = Value =3D (none?)

Phillip Smith

IT Coordinator

Weatherbeeta P/L

8 Moncrief Rd

Nunawading, VIC, 3131

AUSTRALIA

E. phillips@NO-SPAM.weatherbeeta.com.= au

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_003A_01C68ECE.19DFEAB0--