X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id A8B219FA13E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 13 Jun 2006 15:31:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 12765-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 13 Jun 2006 15:31:43 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from web33305.mail.mud.yahoo.com (web33305.mail.mud.yahoo.com
	[68.142.206.120])
	by postgresql.org (Postfix) with SMTP id C8AC89F9316
	for <pgsql-sql@postgresql.org>; Tue, 13 Jun 2006 15:31:42 -0300 (ADT)
Received: (qmail 22584 invoked by uid 60001); 13 Jun 2006 18:31:41 -0000
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=IpZVaGTFLrzj06mE9D9jbMi2GtapybeT/DjGn9UpNY9q4fSVqAzwa+nm7b7EO1DaYnDCw+qDwWErIUmr0Ogk38KIaUi60Y4q/fytAfCxuFtxv5xZc10f2BF9d8Xq6mObf7UCHHWSprY8YmZQk+5oOLQwLm4CX3erxnOrrOrNvf0=
	;
Message-ID: <20060613183141.22580.qmail@web33305.mail.mud.yahoo.com>
Received: from [12.9.179.126] by web33305.mail.mud.yahoo.com via HTTP;
	Tue, 13 Jun 2006 11:31:41 PDT
Date: Tue, 13 Jun 2006 11:31:41 -0700 (PDT)
From: <operationsengineer1@yahoo.com>
Subject: Re: COPY to table with array columns (Longish)
To: operationsengineer1@yahoo.com, phillips@weatherbeeta.com.au,
	pgsql-sql@postgresql.org
In-Reply-To: <20060613182123.17576.qmail@web33305.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/135
X-Sequence-Number: 25371

> > 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