Fwd: COPY to table with array columns (Longish)

Can you provide an example?

Thanks,
Aaron

On 6/11/06, Phillip Smith <phillips(at)weatherbeeta(dot)com(dot)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(at)NO-SPAM(dot)weatherbeeta(dot)com(dot)au
>
>
>