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 951849FA5EA
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 13 Jun 2006 12:05:18 -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 88169-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 13 Jun 2006 12:05:09 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173])
	by postgresql.org (Postfix) with ESMTP id 111659FA12F
	for <pgsql-sql@postgresql.org>; Tue, 13 Jun 2006 12:05:08 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id j40so570111ugd
	for <pgsql-sql@postgresql.org>; Tue, 13 Jun 2006 08:05:07 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth;
	b=lMGjzBugFCZamd5QJ3eSfMyfjc6ToPokQyQz9FIS6CDOTLA0lNi3QqRku1x9fLs1y8zfjSVTsfowzHzYO3bU7k6MQVZpI8FLqf7r/5UjDqL0eOxRYEgYmoFDiNbIwdK5lRdmCsqef7Dtd0+yQrdSEFMqhgJBx8NNKJ/aNA1p4d4=
Received: by 10.66.222.9 with SMTP id u9mr6390885ugg;
	Tue, 13 Jun 2006 08:05:04 -0700 (PDT)
Received: by 10.67.20.12 with HTTP; Tue, 13 Jun 2006 08:05:04 -0700 (PDT)
Message-ID: <bf05e51c0606130805r452fec46y1ef96a371f82b741@mail.gmail.com>
Date: Tue, 13 Jun 2006 10:05:04 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: phillips@weatherbeeta.com.au
Subject: Re: COPY to table with array columns (Longish)
Cc: pgsql-sql@postgresql.org
In-Reply-To: <009801c68eaa$5bf73530$9b0014ac@ITPhil>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <bf05e51c0606122111m78e56216p3f0515cb5885fe1f@mail.gmail.com>
	<009801c68eaa$5bf73530$9b0014ac@ITPhil>
X-Google-Sender-Auth: 95b9c3621bde5713
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/133
X-Sequence-Number: 25369

So how about creating a sys table too:

SYS
   sys_id

ZKCOST
   sys_id,
   zkcost_id,
   zkcost_value

and

ZPRECMPL
   sys_id,
   zprecmpl_id,
   zprecmpl_value

This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want.  The bottom line is, I think it would be much
more efficient storage to determine a way to turn your variable number
of columns into rows of a value table.

For example, I have a web site for role playing games.  Since each
game has different attributes for the characters you play, I need a
flexible way to define the list of attributes and then allow people to
enter the values of those attributes.  Below is a simplified version
of my table structure:

attribute
   attribute_id (PK),
   attribute_name

character
   character_id (PK),
   character_name

character_attribute
   character_attribute_id (PK),
   character_id (FK),
   attribute_id (FK),
   value

It is a little different than your problem but demonstrates how a
variable number of columns (in this case a variable number of
attributes for a character) can be stored with one row representing
each column.

Because I don't understand the context of your problem as well as you
do, you will probably have to determine how to tweak this to meet your
needs.  But I think, from the information you have provided, that this
"pivoted" table approach will work for you.

-Aaron

On 6/13/06, Phillip Smith <phillips@weatherbeeta.com.au> wrote:
> 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> |
>         +-----------+---------+---------+---------+---------+