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

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