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

> 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