Re: [SQL] COPY to table with array columns (Longish)

2006-06-13 Thread Phillip Smith
[SQL] COPY to table with array columns (Longish) I guess I still don't understand... If you take the approach operationsengineer1 and I suggested, you should only need 3 or 4 tables regardless of the number of SYS file records. Good luck with your implementation. -Aaron On 6/13/06, Phi

Re: [SQL] COPY to table with array columns (Longish)

2006-06-13 Thread Aaron Bono
I guess I still don't understand... If you take the approach operationsengineer1 and I suggested, you should only need 3 or 4 tables regardless of the number of SYS file records. Good luck with your implementation. -Aaron On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote: Thanks Aaron - The

Re: [SQL] COPY to table with array columns (Longish)

2006-06-13 Thread Phillip Smith
hanks again, -p -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono Sent: Wednesday, 14 June 2006 1:05 AM To: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] COPY to table with array columns (Longish) So how about creating a sys

Re: [SQL] COPY to table with array columns (Longish)

2006-06-13 Thread operationsengineer1
text | > > a6 | text | > > a7 | text | > > a8 | text | > > a9 | text | > > Indexes: > > "id" PRIMARY KEY, btree (a0) > > > > a0 = primary key - eg, ZPRECMPL or ZKCOST > > > >

Re: [SQL] COPY to table with array columns (Longish)

2006-06-13 Thread operationsengineer1
| >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 &

Re: [SQL] COPY to table with array columns (Longish)

2006-06-13 Thread Aaron Bono
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 ef

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Phillip Smith
RIMARY KEY, btree (a0) a0 = primary key - eg, ZPRECMPL or ZKCOST -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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)

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Aaron Bono
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 zk

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Richard Broersma Jr
> On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote: > > I agree with Tom. Personally I cannot think of a time I would use an > > array column over a child table. Maybe someone can enlighten me on > > when an array column would be a good choice. > > Arrays are a good choice when the data comes

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Rod Taylor
On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote: > I agree with Tom. Personally I cannot think of a time I would use an > array column over a child table. Maybe someone can enlighten me on > when an array column would be a good choice. Arrays are a good choice when the data comes naturally s

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Phillip Smith
om: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono Sent: Tuesday, 13 June 2006 12:58 PM To: Tom Lane Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] COPY to table with array columns (Longish)   I agree with Tom.  Personally I cannot think of a time I woul

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Aaron Bono
I agree with Tom.  Personally I cannot think of a time I would use an array column over a child table.  Maybe someone can enlighten me on when an array column would be a good choice.What language are you using to do the export if I may ask? -AaronOn 6/12/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Phi

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Tom Lane
"Phillip Smith" <[EMAIL PROTECTED]> writes: > The whole sys file is variable length records like this - they range = > from 1 > to over 17,000 fields per record. 17000? I think you really need to rethink your schema. While you could theoretically drop 17000 elements into a PG array column, you w

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Phillip Smith
12:36 AM To: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] COPY to table with array columns (Longish)   Can you provide an example? Thanks, Aaron On 6/11/06, Phillip Smith <[EMAIL PROTECTED] > wrote: Hi All,   Hope someone can help me – our main company system ru

Fwd: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Aaron Bono
Can you provide an example?Thanks,AaronOn 6/11/06, Phillip Smith <[EMAIL PROTECTED] > wrote: Hi All,   Hope someone can help me – our main company system runs on Raining Data PICK/D3 (if anyone familiar with it) which stores records in it's "tables" as variable length items. Every

[SQL] COPY to table with array columns (Longish)

2006-06-11 Thread Phillip Smith
Hi All,   Hope someone can help me – our main company system runs on Raining Data PICK/D3 (if anyone familiar with it) which stores records in it’s “tables” as variable length items. Every item has a unique Primary Key (per table) then each item can have a variable number of fields. These