Re: COPY to table with array columns (Longish) - Mailing list pgsql-sql

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