From: operationsengineer1@yahoo.com
To: phillips@weatherbeeta.com.au
To: pgsql-sql@postgresql.org
Subject: Re: COPY to table with array columns (Longish)
Date: Tue, 13 Jun 2006 11:21:23 -0700 (PDT)
Message-ID: <20060613182123.17576.qmail@web33305.mail.mud.yahoo.com> (raw)
In-Reply-To: <009801c68eaa$5bf73530$9b0014ac@ITPhil>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: operationsengineer1@yahoo.com, phillips@weatherbeeta.com.au
Subject: Re: COPY to table with array columns (Longish)
In-Reply-To: <20060613182123.17576.qmail@web33305.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox