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
