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 850FC9FA42B for ; Mon, 12 Jun 2006 11:42:56 -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 59405-08 for ; Mon, 12 Jun 2006 11:42:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 9C1339F9316 for ; Mon, 12 Jun 2006 11:42:51 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so2538811ugf for ; Mon, 12 Jun 2006 07:42:48 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=SV7M0TVwHH/9sH1w3xlK4q5eA5ydXppWucQ1FZ49rRQKVcj3TW9aeqPwoHN95OhBO5R7K5Ce55qzWTkxRNh/t6mD9gifKqjMlMXXumXVXpS/XIcha638N29EwIHsKNmNyRMfJZKer/jBCAvXF1PTqoDj4mvbRoY8TLtC1diMWp4= Received: by 10.67.100.12 with SMTP id c12mr5256047ugm; Mon, 12 Jun 2006 07:42:48 -0700 (PDT) Received: by 10.67.20.12 with HTTP; Mon, 12 Jun 2006 07:42:48 -0700 (PDT) Message-ID: Date: Mon, 12 Jun 2006 09:42:48 -0500 From: "Aaron Bono" To: pgsql-sql@postgresql.org Subject: Fwd: COPY to table with array columns (Longish) In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6208_26827235.1150123368610" References: <004701c68dc0$fc721510$9b0014ac@ITPhil> X-Google-Sender-Auth: 1772ca882e900280 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/121 X-Sequence-Number: 25357 ------=_Part_6208_26827235.1150123368610 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Can you provide an example? Thanks, Aaron On 6/11/06, Phillip Smith wrote: > > Hi All, > > > > Hope someone can help me =96 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 C= har > 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=85. Yet) and hasn't been a > problem so far =96 everything like stock and purchase orders, sales order= s 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 impo= rt > 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 3= 00. > 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 =96 the pk= ey > 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 t= o > 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 > > > ------=_Part_6208_26827235.1150123368610 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Can you provide an example?

= Thanks,
Aaron


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

Hi All,

 

Hope someone can help me =96 our main company system runs on Raining Data PICK/D3 (if anyone familiar with it) which stores reco= rds 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=85. Yet) and hasn't been a problem so far =96 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 eac= h 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 =96 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 inser= t 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        &nbs= p;  =3D     PICK/D3     =3D&nb= sp;    Postgres

D= rive             =3D=      Account     =3D  &nb= sp;  Database

D= irectory         =3D   &n= bsp; File        =3D   &n= bsp; Table

F= ile              =3D=      Item        =3D=      Row

L= ine in text file =3D     Attribute   =3D = ;    Field

(= none)            =3D = ;    Value       =3D &nbs= p;   Array Element (?)

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

 

Phillip Smith

IT Coordinator

Weatherbeeta P/L

8 Moncrief Rd

Nunawading, VIC, 3131

AUSTRALIA

 

E. phi= llips@NO-SPAM.weatherbeeta.com.au

 


------=_Part_6208_26827235.1150123368610--