DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=v6eQHcslyQm5rteL8q8gWAtZMpDxTqCeDrVqMlfIqLlE1F58q/GIWNx6WlX6YlgNBm7EFsro2GngGEImczbPanYmgwT5W83qNbbLZkHke0uGfZoS/pFw2uggodSbCsT+KjY1XCUC05uwpEwlfuC4lzJv4eAMiKPqQhwzeB8s8lI=
	;
Message-ID: <20060613182123.17576.qmail@web33305.mail.mud.yahoo.com>
Date: Tue, 13 Jun 2006 11:21:23 -0700 (PDT)
From: <operationsengineer1@yahoo.com>
Subject: Re: COPY to table with array columns (Longish)
To: phillips@weatherbeeta.com.au, pgsql-sql@postgresql.org
In-Reply-To: <009801c68eaa$5bf73530$9b0014ac@ITPhil>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> 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