Re: COPY to table with array columns (Longish)

> > 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(at)postgresql(dot)org
> > [mailto:pgsql-sql-owner(at)postgresql(dot)org]
> > On Behalf Of Aaron Bono
> > Sent: Tuesday, 13 June 2006 2:12 PM
> > To: pgsql-sql(at)postgresql(dot)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(at)weatherbeeta(dot)com(dot)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