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

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