From: Aaron Bono <postgresql@aranya.com>
To: pgsql-sql@postgresql.org
Subject: Fwd: COPY to table with array columns (Longish)
Date: Mon, 12 Jun 2006 09:42:48 -0500
Message-ID: <bf05e51c0606120742n2bd0b70md86e1d33eeaab208@mail.gmail.com> (raw)
In-Reply-To: <bf05e51c0606120736u65562a37td89ed16dd41ccc7d@mail.gmail.com>
References: <004701c68dc0$fc721510$9b0014ac@ITPhil>
<bf05e51c0606120736u65562a37td89ed16dd41ccc7d@mail.gmail.com>
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 = PICK/D3 = Postgres*
>
> Drive = Account = Database
>
> Directory = File = Table
>
> File = Item = Row
>
> Line in text file = Attribute = Field
>
> (none) = Value = Array Element (?)
>
> (none) = Sub Value = (none?)
>
>
>
> Phillip Smith
>
> IT Coordinator
>
> Weatherbeeta P/L
>
> 8 Moncrief Rd
>
> Nunawading, VIC, 3131
>
> AUSTRALIA
>
>
>
> E. phillips@NO-SPAM.weatherbeeta.com.au
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com
Subject: Re: Fwd: COPY to table with array columns (Longish)
In-Reply-To: <bf05e51c0606120742n2bd0b70md86e1d33eeaab208@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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