Thread: COPY to table with array columns (Longish)

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi All,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hope someone can help me – our main company system runs on Raining Data PICK/D3 (if anyone familiar
withit) which stores records in it’s “tables” as variable length items. Every item has a unique Primary Key (per table)
theneach item can have a variable number of fields. These fields are delimited by Char 254, then each field can have
sub-valuesdelimited by Char 253, then sub-sub-values delimited by Char 252.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Anyway, we are trying to export everything to Postgres for reporting and querying etc (not to
actuallyrun the system…. Yet) and hasn’t been a problem so far – everything like stock and purchase orders, sales
ordersetc can pretty easily be turned in to a flat file with standard number of columns and consistent data. We
truncateevery table each night then import that latest TSV export from D3 using a COPY command.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The problem arises with tables like our SYS table which store generic system data, so one record
couldhave 3 fields, but the next could have 300. The only way I can work out how to export multi-valued data like this
toPostgres is to use an array column. So the table has 2 columns – the pkey and a data array.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">How do I get this imported to the truncated table each night? At the moment I think my best option
isto modify the export for the SYS table to call PSQL and use standard SQL INSERT statements to directly insert it
insteadof exporting to a flat file, then import to Postgres.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks all,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">-p</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">For those who are interested, or if it might help, here’s a rough comparison of the database
structureof D3:</span></font><p class="MsoNormal"><b><font face="Courier New" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New";font-weight:bold">Windows           =     PICK/D3     =    
Postgres</span></font></b><pclass="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Drive             =     Account     =     Database</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Directory         =     File        =     Table</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">File              =     Item        =     Row</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Line in text file =     Attribute   =     Field</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">(none)            =     Value       =     Array Element (?)</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">(none)            =     Sub Value   =     (none?)</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">PhillipSmith</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">IT Coordinator</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Weatherbeeta P/L</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">8 Moncrief Rd</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Nunawading, VIC, 3131</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">AUSTRALIA</span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">E. phillips@NO-SPAM.weatherbeeta.com.au</span></font><p class="MsoNormal"><font face="Times New
Roman"size="3"><span style="font-size: 
12.0pt"> </span></font></div><br /><p><b>*******************Confidentiality and Privilege
Notice*******************</b><p>The material contained in this message is privileged and confidential to the addressee.
Ifyou are not the addressee indicated in this message or responsible for delivery of the message to such person, you
maynot copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
<p>Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither
givennor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for
direct,indirect or consequential loss arising from transmission of this message or any attachments <br /> 

Can you provide an example?

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

"Phillip Smith" <phillips@weatherbeeta.com.au> writes:
> The whole sys file is variable length records like this - they range =
> from 1
> to over 17,000 fields per record.

17000?  I think you really need to rethink your schema.  While you could
theoretically drop 17000 elements into a PG array column, you wouldn't
like the performance --- it'd be almost unsearchable for instance.

I'd think about two tables, one with a single row for each SYS record
from the original, and one with one row for each detail item (the
invoice numbers in this case).  With suitable indexes and a foreign key
constraint, this will perform a lot better than an array-based
translation.

And no, in neither case will you be able to import that file without
massaging it first.
        regards, tom lane

I agree with Tom.  Personally I cannot think of a time I would use an array column over a child table.  Maybe someone can enlighten me on when an array column would be a good choice.

What language are you using to do the export if I may ask?

-Aaron

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote:
> I agree with Tom.  Personally I cannot think of a time I would use an
> array column over a child table.  Maybe someone can enlighten me on
> when an array column would be a good choice.

Arrays are a good choice when the data comes naturally segmented.

I regularly store sentences or sentence fragments as an array of words
when I don't care about whitespace (whitespace should be regenerated on
the output). Consider meta-keywords for a webpage for example.

This is similar process as removing all formatting from phone numbers
before storage and reformatting for display to the user again.

(123)456-7890 might be stored as '1234567890'.

"A\tcat in   the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the',
'hat'].

It makes comparisons and uniqueness much easier to deal with if you
remove garbage from the data prior to storage.

> On 6/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>         "Phillip Smith" <phillips@weatherbeeta.com.au> writes:
>         > The whole sys file is variable length records like this -
>         they range =
>         > from 1
>         > to over 17,000 fields per record. 
>         
>         17000?  I think you really need to rethink your schema.  While
>         you could
>         theoretically drop 17000 elements into a PG array column, you
>         wouldn't
>         like the performance --- it'd be almost unsearchable for
>         instance. 
>         
>         I'd think about two tables, one with a single row for each SYS
>         record
>         from the original, and one with one row for each detail item
>         (the
>         invoice numbers in this case).  With suitable indexes and a
>         foreign key 
>         constraint, this will perform a lot better than an array-based
>         translation.
>         
>         And no, in neither case will you be able to import that file
>         without
>         massaging it first.
>         
>                                 regards, tom lane 
> 
-- 

> On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote:
> > I agree with Tom.  Personally I cannot think of a time I would use an
> > array column over a child table.  Maybe someone can enlighten me on
> > when an array column would be a good choice.
> 
> Arrays are a good choice when the data comes naturally segmented.
> 

Also, for a case and point, some of the postgresql system tables use arrays.  I suppose that these
would be examples were the core develops felt arrays were a good fit.

Regards,
Richard Broersma Jr.

I think two tables should suffice: ZKCOST and ZPRECMPL.

So you would have

ZKCOST   zkcost_id,   zkcost_value

and

ZPRECMPL   zkcost_id,   zprecmpl_id,   zprecmpl_value

where zkcost_id is the primary key for ZKCOST and zkcost_id,
zprecmpl_id together are the primary key for ZPRECMPL and zkcost_id is
a foreign key from ZPRECMPL to ZKCOST.

That will work won't it?

-Aaron

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

> So you're suggesting creating a child table for each SYS record? Ie, a table called "ZPRECMPL" etc?

Not quite... ZKCOST and ZPRECMPL are two completely different things. They
have no relation except they're both stored in the SYS table in D3.

If we put it in a tree:SYS | +- ZKCOST |     \- <value> | +- ZPRECMPL |     +- <value> |     +- <value> |     +-
<value>|     \- <value> 

or table:SYS+-----------+---------+---------+---------+---------+| ZKCOST    | <value> |         |         |         ||
ZPRECMPL | <value> | <value> | <value> | <value> |+-----------+---------+---------+---------+---------+ 

So other than a variable-element array, the only other way would be to
create a table with a column count equal to or greater than the maximum
amount of values (call that value 'Y') that any sys item holds then if a
particular record (eg, ZKCOST) has less values than Y, fill the rest of the
columns with blanks (as above).

That's what I've done at the moment, but only for 9 columns, so anything
over 9 fields will be truncated past and including field 10:wbau=# \d sys    Table "public.sys" Column | Type |
Modifiers--------+------+-----------a0     | text | not null a1     | text | a2     | text | a3     | text | a4     |
text| a5     | text | a6     | text | a7     | text | a8     | text | a9     | text |Indexes:    "id" PRIMARY KEY,
btree(a0) 

a0 = primary key - eg, ZPRECMPL or ZKCOST

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Aaron Bono
Sent: Tuesday, 13 June 2006 2:12 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

I think two tables should suffice: ZKCOST and ZPRECMPL.

So you would have

ZKCOST   zkcost_id,   zkcost_value

and

ZPRECMPL   zkcost_id,   zprecmpl_id,   zprecmpl_value

where zkcost_id is the primary key for ZKCOST and zkcost_id,
zprecmpl_id together are the primary key for ZPRECMPL and zkcost_id is
a foreign key from ZPRECMPL to ZKCOST.

That will work won't it?

-Aaron

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

> So you're suggesting creating a child table for each SYS record? Ie, a
table called "ZPRECMPL" etc?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

So how about creating a sys table too:

SYS  sys_id

ZKCOST  sys_id,  zkcost_id,  zkcost_value

and

ZPRECMPL  sys_id,  zprecmpl_id,  zprecmpl_value

This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want.  The bottom line is, I think it would be much
more efficient storage to determine a way to turn your variable number
of columns into rows of a value table.

For example, I have a web site for role playing games.  Since each
game has different attributes for the characters you play, I need a
flexible way to define the list of attributes and then allow people to
enter the values of those attributes.  Below is a simplified version
of my table structure:

attribute  attribute_id (PK),  attribute_name

character  character_id (PK),  character_name

character_attribute  character_attribute_id (PK),  character_id (FK),  attribute_id (FK),  value

It is a little different than your problem but demonstrates how a
variable number of columns (in this case a variable number of
attributes for a character) can be stored with one row representing
each column.

Because I don't understand the context of your problem as well as you
do, you will probably have to determine how to tweak this to meet your
needs.  But I think, from the information you have provided, that this
"pivoted" table approach will work for you.

-Aaron

On 6/13/06, Phillip Smith <phillips@weatherbeeta.com.au> wrote:
> Not quite... ZKCOST and ZPRECMPL are two completely different things. They
> have no relation except they're both stored in the SYS table in D3.
>
> If we put it in a tree:
>         SYS
>          |
>          +- ZKCOST
>          |     \- <value>
>          |
>          +- ZPRECMPL
>          |     +- <value>
>          |     +- <value>
>          |     +- <value>
>          |     \- <value>
>
> or table:
>         SYS
>         +-----------+---------+---------+---------+---------+
>         | ZKCOST    | <value> |         |         |         |
>         | ZPRECMPL  | <value> | <value> | <value> | <value> |
>         +-----------+---------+---------+---------+---------+

> Not quite... ZKCOST and ZPRECMPL are two completely
> different things. They
> have no relation except they're both stored in the
> SYS table in D3.
> 
> If we put it in a tree:
>     SYS
>      |
>      +- ZKCOST
>      |     \- <value>
>      |
>      +- ZPRECMPL
>      |     +- <value>
>      |     +- <value>
>      |     +- <value>
>      |     \- <value>
> 
> or table:
>     SYS
> 
>
+-----------+---------+---------+---------+---------+
>     | ZKCOST    | <value> |         |         |        
> |
>     | ZPRECMPL  | <value> | <value> | <value> | <value>
> |
> 
>
+-----------+---------+---------+---------+---------+
> 
> So other than a variable-element array, the only
> other way would be to
> create a table with a column count equal to or
> greater than the maximum
> amount of values (call that value 'Y') that any sys
> item holds then if a
> particular record (eg, ZKCOST) has less values than
> Y, fill the rest of the
> columns with blanks (as above).
> 
> That's what I've done at the moment, but only for 9
> columns, so anything
> over 9 fields will be truncated past and including
> field 10:
>     wbau=# \d sys
>         Table "public.sys"
>      Column | Type | Modifiers
>     --------+------+-----------
>      a0     | text | not null
>      a1     | text |
>      a2     | text |
>      a3     | text |
>      a4     | text |
>      a5     | text |
>      a6     | text |
>      a7     | text |
>      a8     | text |
>      a9     | text |
>     Indexes:
>         "id" PRIMARY KEY, btree (a0)
> 
> a0 = primary key - eg, ZPRECMPL or ZKCOST
> 
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of Aaron Bono
> Sent: Tuesday, 13 June 2006 2:12 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] COPY to table with array columns
> (Longish)
> 
> I think two tables should suffice: ZKCOST and
> ZPRECMPL.
> 
> So you would have
> 
> ZKCOST
>     zkcost_id,
>     zkcost_value
> 
> and
> 
> ZPRECMPL
>     zkcost_id,
>     zprecmpl_id,
>     zprecmpl_value
> 
> where zkcost_id is the primary key for ZKCOST and
> zkcost_id,
> zprecmpl_id together are the primary key for
> ZPRECMPL and zkcost_id is
> a foreign key from ZPRECMPL to ZKCOST.
> 
> That will work won't it?
> 
> -Aaron
> 
> On 6/12/06, Phillip Smith
> <phillips@weatherbeeta.com.au> wrote:
> 
> > So you're suggesting creating a child table for
> each SYS record? Ie, a
> table called "ZPRECMPL" etc?

if the data is unrelated, then the data should be
separated (in a perfect world).

can you convert into the following form:

TABLE_ZKCOST
zkcost_id
zkcost_value

TABLE_ZPRECMPL
zprecmpl_id

TABLE_ZPRECMPL_DATA
zprecmpl_data_id
zprecmpl_id
zprecmpl_value


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

> > Not quite... ZKCOST and ZPRECMPL are two
> completely
> > different things. They
> > have no relation except they're both stored in the
> > SYS table in D3.
> > 
> > If we put it in a tree:
> >     SYS
> >      |
> >      +- ZKCOST
> >      |     \- <value>
> >      |
> >      +- ZPRECMPL
> >      |     +- <value>
> >      |     +- <value>
> >      |     +- <value>
> >      |     \- <value>
> > 
> > or table:
> >     SYS
> > 
> >
>
+-----------+---------+---------+---------+---------+
> >     | ZKCOST    | <value> |         |         |      
>  
> > |
> >     | ZPRECMPL  | <value> | <value> | <value> |
> <value>
> > |
> > 
> >
>
+-----------+---------+---------+---------+---------+
> > 
> > So other than a variable-element array, the only
> > other way would be to
> > create a table with a column count equal to or
> > greater than the maximum
> > amount of values (call that value 'Y') that any
> sys
> > item holds then if a
> > particular record (eg, ZKCOST) has less values
> than
> > Y, fill the rest of the
> > columns with blanks (as above).
> > 
> > That's what I've done at the moment, but only for
> 9
> > columns, so anything
> > over 9 fields will be truncated past and including
> > field 10:
> >     wbau=# \d sys
> >         Table "public.sys"
> >      Column | Type | Modifiers
> >     --------+------+-----------
> >      a0     | text | not null
> >      a1     | text |
> >      a2     | text |
> >      a3     | text |
> >      a4     | text |
> >      a5     | text |
> >      a6     | text |
> >      a7     | text |
> >      a8     | text |
> >      a9     | text |
> >     Indexes:
> >         "id" PRIMARY KEY, btree (a0)
> > 
> > a0 = primary key - eg, ZPRECMPL or ZKCOST
> > 
> > -----Original Message-----
> > From: pgsql-sql-owner@postgresql.org
> > [mailto:pgsql-sql-owner@postgresql.org]
> > On Behalf Of Aaron Bono
> > Sent: Tuesday, 13 June 2006 2:12 PM
> > To: pgsql-sql@postgresql.org
> > Subject: Re: [SQL] COPY to table with array
> columns
> > (Longish)
> > 
> > I think two tables should suffice: ZKCOST and
> > ZPRECMPL.
> > 
> > So you would have
> > 
> > ZKCOST
> >     zkcost_id,
> >     zkcost_value
> > 
> > and
> > 
> > ZPRECMPL
> >     zkcost_id,
> >     zprecmpl_id,
> >     zprecmpl_value
> > 
> > where zkcost_id is the primary key for ZKCOST and
> > zkcost_id,
> > zprecmpl_id together are the primary key for
> > ZPRECMPL and zkcost_id is
> > a foreign key from ZPRECMPL to ZKCOST.
> > 
> > That will work won't it?
> > 
> > -Aaron
> > 
> > On 6/12/06, Phillip Smith
> > <phillips@weatherbeeta.com.au> wrote:
> > 
> > > So you're suggesting creating a child table for
> > each SYS record? Ie, a
> > table called "ZPRECMPL" etc?
> 
> if the data is unrelated, then the data should be
> separated (in a perfect world).
> 
> can you convert into the following form:
> 
> TABLE_ZKCOST
> zkcost_id
> zkcost_value
> 
> TABLE_ZPRECMPL
> zprecmpl_id
> 
> TABLE_ZPRECMPL_DATA
> zprecmpl_data_id
> zprecmpl_id
> zprecmpl_value

just read Aaron's latest post - it is best to do as he
suggested and link back to the sys table.

TABLE_SYS sys_id

TABLE_ZKCOST zkcost_id sys_id zkcost_value

TABLE_ZPRECMPL zprecmpl_id sys_id

TABLE_ZPRECMPL_DATA zprecmpl_data_id zprecmpl_id zprecmpl_value

the difference, and i'm not sure it is significant, is
that the above links the ZPRECMPL_DATA back to
ZPRECMPL, then it links ZPRECMPL back to SYS.

that's how my head wraps around this problem and i
think it should work alright as long as you can
massage the data into this format.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Thanks Aaron - There are currently 8175 records in my SYS file - I might
need to go with this approach but be selective about which items I export so
I don't end up with 8000 tables related to SYS! There's probably a lot of
**** in there that doesn't actually need to be exported.

Thanks again,
-p

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 1:05 AM
To: phillips@weatherbeeta.com.au
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

So how about creating a sys table too:

SYS  sys_id

ZKCOST  sys_id,  zkcost_id,  zkcost_value

and

ZPRECMPL  sys_id,  zprecmpl_id,  zprecmpl_value

This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want.  The bottom line is, I think it would be much
more efficient storage to determine a way to turn your variable number
of columns into rows of a value table.

For example, I have a web site for role playing games.  Since each
game has different attributes for the characters you play, I need a
flexible way to define the list of attributes and then allow people to
enter the values of those attributes.  Below is a simplified version
of my table structure:

attribute  attribute_id (PK),  attribute_name

character  character_id (PK),  character_name

character_attribute  character_attribute_id (PK),  character_id (FK),  attribute_id (FK),  value

It is a little different than your problem but demonstrates how a
variable number of columns (in this case a variable number of
attributes for a character) can be stored with one row representing
each column.

Because I don't understand the context of your problem as well as you
do, you will probably have to determine how to tweak this to meet your
needs.  But I think, from the information you have provided, that this
"pivoted" table approach will work for you.

-Aaron

On 6/13/06, Phillip Smith <phillips@weatherbeeta.com.au> wrote:
> Not quite... ZKCOST and ZPRECMPL are two completely different things. They
> have no relation except they're both stored in the SYS table in D3.
>
> If we put it in a tree:
>         SYS
>          |
>          +- ZKCOST
>          |     \- <value>
>          |
>          +- ZPRECMPL
>          |     +- <value>
>          |     +- <value>
>          |     +- <value>
>          |     \- <value>
>
> or table:
>         SYS
>         +-----------+---------+---------+---------+---------+
>         | ZKCOST    | <value> |         |         |         |
>         | ZPRECMPL  | <value> | <value> | <value> | <value> |
>         +-----------+---------+---------+---------+---------+

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

I guess I still don't understand...

If you take the approach operationsengineer1 and I suggested, you
should only need 3 or 4 tables regardless of the number of SYS file
records.

Good luck with your implementation.

-Aaron

On 6/13/06, Phillip Smith <phillips@weatherbeeta.com.au> wrote:
> Thanks Aaron - There are currently 8175 records in my SYS file - I might
> need to go with this approach but be selective about which items I export so
> I don't end up with 8000 tables related to SYS! There's probably a lot of
> **** in there that doesn't actually need to be exported.
>
> Thanks again,
> -p

No, it was me that didn't understand!! But I do now - quite simple really!

Cheers,
-p

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 10:41 AM
To: phillips@weatherbeeta.com.au
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

I guess I still don't understand...

If you take the approach operationsengineer1 and I suggested, you
should only need 3 or 4 tables regardless of the number of SYS file
records.

Good luck with your implementation.

-Aaron

On 6/13/06, Phillip Smith <phillips@weatherbeeta.com.au> wrote:
> Thanks Aaron - There are currently 8175 records in my SYS file - I might
> need to go with this approach but be selective about which items I export
so
> I don't end up with 8000 tables related to SYS! There's probably a lot of
> **** in there that doesn't actually need to be exported.
>
> Thanks again,
> -p

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to
majordomo@postgresql.orgso that your      message can get through to the mailing list cleanly 


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments