From: operationsengineer1@yahoo.com
To: 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:31:41 -0700 (PDT)
Message-ID: <20060613183141.22580.qmail@web33305.mail.mud.yahoo.com> (raw)
In-Reply-To: <20060613182123.17576.qmail@web33305.mail.mud.yahoo.com>
> > 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
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: <20060613183141.22580.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