Thread: COPY to table with array columns (Longish)

COPY to table with array columns (Longish)

From
"Phillip Smith"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi All,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hope someone can help me – our main company system runs on Raining Data PICK/D3 (if anyone familiar
withit) which stores records in it’s “tables” as variable length items. Every item has a unique Primary Key (per table)
theneach item can have a variable number of fields. These fields are delimited by Char 254, then each field can have
sub-valuesdelimited by Char 253, then sub-sub-values delimited by Char 252.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Anyway, we are trying to export everything to Postgres for reporting and querying etc (not to
actuallyrun the system…. Yet) and hasn’t been a problem so far – everything like stock and purchase orders, sales
ordersetc can pretty easily be turned in to a flat file with standard number of columns and consistent data. We
truncateevery table each night then import that latest TSV export from D3 using a COPY command.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The problem arises with tables like our SYS table which store generic system data, so one record
couldhave 3 fields, but the next could have 300. The only way I can work out how to export multi-valued data like this
toPostgres is to use an array column. So the table has 2 columns – the pkey and a data array.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">How do I get this imported to the truncated table each night? At the moment I think my best option
isto modify the export for the SYS table to call PSQL and use standard SQL INSERT statements to directly insert it
insteadof exporting to a flat file, then import to Postgres.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks all,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">-p</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">For those who are interested, or if it might help, here’s a rough comparison of the database
structureof D3:</span></font><p class="MsoNormal"><b><font face="Courier New" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New";font-weight:bold">Windows           =     PICK/D3     =    
Postgres</span></font></b><pclass="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Drive             =     Account     =     Database</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Directory         =     File        =     Table</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">File              =     Item        =     Row</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Line in text file =     Attribute   =     Field</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">(none)            =     Value       =     Array Element (?)</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">(none)            =     Sub Value   =     (none?)</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">PhillipSmith</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">IT Coordinator</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Weatherbeeta P/L</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">8 Moncrief Rd</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Nunawading, VIC, 3131</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">AUSTRALIA</span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">E. phillips@NO-SPAM.weatherbeeta.com.au</span></font><p class="MsoNormal"><font face="Times New
Roman"size="3"><span style="font-size: 
12.0pt"> </span></font></div><br /><p><b>*******************Confidentiality and Privilege
Notice*******************</b><p>The material contained in this message is privileged and confidential to the addressee.
Ifyou are not the addressee indicated in this message or responsible for delivery of the message to such person, you
maynot copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
<p>Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither
givennor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for
direct,indirect or consequential loss arising from transmission of this message or any attachments <br /> 

Fwd: COPY to table with array columns (Longish)

From
"Aaron Bono"
Date:
Can you provide an example?

Thanks,
Aaron


On 6/11/06, Phillip Smith <phillips@weatherbeeta.com.au > 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 item has a unique Primary Key (per table) then each item can have a variable number of fields. These fields are delimited by Char 254, then each field can have sub-values delimited by Char 253, then sub-sub-values delimited by Char 252.

 

Anyway, we are trying to export everything to Postgres for reporting and querying etc (not to actually run the system…. Yet) and hasn't been a problem so far – everything like stock and purchase orders, sales orders etc can pretty easily be turned in to a flat file with standard number of columns and consistent data. We truncate every table each night then import that latest TSV export from D3 using a COPY command.

 

The problem arises with tables like our SYS table which store generic system data, so one record could have 3 fields, but the next could have 300. The only way I can work out how to export multi-valued data like this to Postgres is to use an array column. So the table has 2 columns – the pkey and a data array.

 

How do I get this imported to the truncated table each night? At the moment I think my best option is to modify the export for the SYS table to call PSQL and use standard SQL INSERT statements to directly insert it instead of exporting to a flat file, then import to Postgres.

 

Thanks all,

-p

 

For those who are interested, or if it might help, here's a rough comparison of the database structure of D3:

Windows           =     PICK/D3     =     Postgres

Drive             =     Account     =     Database

Directory         =     File        =     Table

File              =     Item        =     Row

Line in text file =     Attribute   =     Field

(none)            =     Value       =     Array Element (?)

(none)            =     Sub Value   =     (none?)

 

Phillip Smith

IT Coordinator

Weatherbeeta P/L

8 Moncrief Rd

Nunawading, VIC, 3131

AUSTRALIA

 

E. phillips@NO-SPAM.weatherbeeta.com.au

 


Re: COPY to table with array columns (Longish)

From
"Phillip Smith"
Date:

No problem!

 

SYS  'ZKCOST' size = 21

01 2750

This is a simple record in the SYS file. Primary Key is “ZKCOST” and the Data would be 1 element with a value of 2750.

 

SYS  'ZPRECMPL' size = 2069                                                  O

01 928898

02 928899

03 928900

04 928901

05 928902

06 928903

07 928904

08 928907

09 928908

10 928909

11 928910

12 928915

13 928916

14 928917

15 928918

16 928919

17 928920

18 928921

19 928925

20 928926

21 928927

<snip>

Another SYS record – this is a list of invoices that are waiting to be confirmed and therefore obviously constantly change with additions and deletions. So in the SQL this would need to have a Primary Key of “ZPRECMPL” and 21 elements in the array, each with an invoice number… (well, there’s actually 293 in there at the moment, but I don’t think I need to fill up the e-mail with all of them!)

 

The whole sys file is variable length records like this – they range from 1 to over 17,000 fields per record.

 

Hope this helps,

Thanks,

-p

 

-----Original Message-----
From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent: Tuesday, 13 June 2006 12:36 AM
To: phillips@weatherbeeta.com.au
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 <phillips@weatherbeeta.com.au > 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 item has a unique Primary Key (per table) then each item can have a variable number of fields. These fields are delimited by Char 254, then each field can have sub-values delimited by Char 253, then sub-sub-values delimited by Char 252.

 

Anyway, we are trying to export everything to Postgres for reporting and querying etc (not to actually run the system…. Yet) and hasn't been a problem so far – everything like stock and purchase orders, sales orders etc can pretty easily be turned in to a flat file with standard number of columns and consistent data. We truncate every table each night then import that latest TSV export from D3 using a COPY command.

 

The problem arises with tables like our SYS table which store generic system data, so one record could have 3 fields, but the next could have 300. The only way I can work out how to export multi-valued data like this to Postgres is to use an array column. So the table has 2 columns – the pkey and a data array.

 

How do I get this imported to the truncated table each night? At the moment I think my best option is to modify the export for the SYS table to call PSQL and use standard SQL INSERT statements to directly insert it instead of exporting to a flat file, then import to Postgres.

 

Thanks all,

-p

 

For those who are interested, or if it might help, here's a rough comparison of the database structure of D3:

Windows           =     PICK/D3     =     Postgres

Drive             =     Account     =     Database

Directory         =     File        =     Table

File              =     Item        =     Row

Line in text file =     Attribute   =     Field

(none)            =     Value       =     Array Element (?)

(none)            =     Sub Value   =     (none?)

 

Phillip Smith

IT Coordinator

Weatherbeeta P/L

8 Moncrief Rd

Nunawading, VIC, 3131

AUSTRALIA

 

E. phillips@NO-SPAM.weatherbeeta.com.au

 


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

Re: COPY to table with array columns (Longish)

From
Tom Lane
Date:
"Phillip Smith" <phillips@weatherbeeta.com.au> 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 wouldn't
like the performance --- it'd be almost unsearchable for instance.

I'd think about two tables, one with a single row for each SYS record
from the original, and one with one row for each detail item (the
invoice numbers in this case).  With suitable indexes and a foreign key
constraint, this will perform a lot better than an array-based
translation.

And no, in neither case will you be able to import that file without
massaging it first.
        regards, tom lane


Re: COPY to table with array columns (Longish)

From
"Aaron Bono"
Date:
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?

-Aaron

On 6/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Phillip Smith" <phillips@weatherbeeta.com.au> 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 wouldn't
like the performance --- it'd be almost unsearchable for instance.

I'd think about two tables, one with a single row for each SYS record
from the original, and one with one row for each detail item (the
invoice numbers in this case).  With suitable indexes and a foreign key
constraint, this will perform a lot better than an array-based
translation.

And no, in neither case will you be able to import that file without
massaging it first.

                        regards, tom lane

Re: COPY to table with array columns (Longish)

From
"Phillip Smith"
Date:

The export from the D3 system is written in PICK BASIC – similar to ‘normal’ BASIC.

 

This currently exports Attribute (field) 1 to 9 of each SYS record with a Primary Key starting with “Z” (‘]’ is a wildcard in the SSELECT statement)

153 EXECUTE 'SSELECT SYS WITH A0 "Z]"' CAPTURING JUNK

154 LOOP

155    READNEXT SYS.ID THEN

156       READ SYS.REC FROM SYS, SYS.ID ELSE

157          SYS.ID = 'XXXXXX'

158       END

159       *

160       OUTLINE = SYS.ID

161       FOR SYS.SUB = 1 TO 9

162          OUTLINE = OUTLINE:AM:SYS.REC<SYS.SUB,1>

163       NEXT SYS.SUB

164       *

165       CONVERT CHAR(252) TO "" IN OUTLINE

166       CONVERT CHAR(92) TO "" IN OUTLINE

167       CONVERT CHAR(34) TO "" IN OUTLINE

168       OUTLINE = OCONV(OUTLINE, "MCU")

169       N=%FPUTS(OUTLINE:NL, (CHAR*)STREAM)

170    END ELSE

171       SYS.ID = 'XXXXXX'

172    END

173 490 NULL

174 UNTIL SYS.ID = 'XXXXXX' DO REPEAT

 

So you’re suggesting creating a child table for each SYS record? Ie, a table called “ZPRECMPL” etc?

 

Thanks for your input guys,

Cheers,

-p

 

-----Original Message-----
From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent:
Tuesday, 13 June 2006 12:58 PM
To:
Tom Lane
Cc: phillips@weatherbeeta.com.au; 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 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?

-Aaron

On 6/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Phillip Smith" <phillips@weatherbeeta.com.au> 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 wouldn't
like the performance --- it'd be almost unsearchable for instance.

I'd think about two tables, one with a single row for each SYS record
from the original, and one with one row for each detail item (the
invoice numbers in this case).  With suitable indexes and a foreign key
constraint, this will perform a lot better than an array-based
translation.

And no, in neither case will you be able to import that file without
massaging it first.

                        regards, tom lane

 


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

Re: COPY to table with array columns (Longish)

From
Rod Taylor
Date:
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 segmented.

I regularly store sentences or sentence fragments as an array of words
when I don't care about whitespace (whitespace should be regenerated on
the output). Consider meta-keywords for a webpage for example.

This is similar process as removing all formatting from phone numbers
before storage and reformatting for display to the user again.

(123)456-7890 might be stored as '1234567890'.

"A\tcat in   the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the',
'hat'].

It makes comparisons and uniqueness much easier to deal with if you
remove garbage from the data prior to storage.

> On 6/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>         "Phillip Smith" <phillips@weatherbeeta.com.au> 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
>         wouldn't
>         like the performance --- it'd be almost unsearchable for
>         instance. 
>         
>         I'd think about two tables, one with a single row for each SYS
>         record
>         from the original, and one with one row for each detail item
>         (the
>         invoice numbers in this case).  With suitable indexes and a
>         foreign key 
>         constraint, this will perform a lot better than an array-based
>         translation.
>         
>         And no, in neither case will you be able to import that file
>         without
>         massaging it first.
>         
>                                 regards, tom lane 
> 
-- 



Re: COPY to table with array columns (Longish)

From
Richard Broersma Jr
Date:
> 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 segmented.
> 

Also, for a case and point, some of the postgresql system tables use arrays.  I suppose that these
would be examples were the core develops felt arrays were a good fit.

Regards,
Richard Broersma Jr.



Re: COPY to table with array columns (Longish)

From
"Aaron Bono"
Date:
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?


Re: COPY to table with array columns (Longish)

From
"Phillip Smith"
Date:
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?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments


Re: COPY to table with array columns (Longish)

From
"Aaron Bono"
Date:
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> |
>         +-----------+---------+---------+---------+---------+


Re: COPY to table with array columns (Longish)

From
Date:
> 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 


Re: COPY to table with array columns (Longish)

From
Date:
> > 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 


Re: COPY to table with array columns (Longish)

From
"Phillip Smith"
Date:
Thanks Aaron - There are currently 8175 records in my SYS file - I might
need to go with this approach but be selective about which items I export so
I don't end up with 8000 tables related to SYS! There's probably a lot of
**** in there that doesn't actually need to be exported.

Thanks again,
-p

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 1:05 AM
To: phillips@weatherbeeta.com.au
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments


Re: COPY to table with array columns (Longish)

From
"Aaron Bono"
Date:
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 <phillips@weatherbeeta.com.au> wrote:
> Thanks Aaron - There are currently 8175 records in my SYS file - I might
> need to go with this approach but be selective about which items I export so
> I don't end up with 8000 tables related to SYS! There's probably a lot of
> **** in there that doesn't actually need to be exported.
>
> Thanks again,
> -p


Re: COPY to table with array columns (Longish)

From
"Phillip Smith"
Date:
No, it was me that didn't understand!! But I do now - quite simple really!

Cheers,
-p

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 10:41 AM
To: phillips@weatherbeeta.com.au
Cc: pgsql-sql@postgresql.org
Subject: Re: [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, Phillip Smith <phillips@weatherbeeta.com.au> wrote:
> Thanks Aaron - There are currently 8175 records in my SYS file - I might
> need to go with this approach but be selective about which items I export
so
> I don't end up with 8000 tables related to SYS! There's probably a lot of
> **** in there that doesn't actually need to be exported.
>
> Thanks again,
> -p

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to
majordomo@postgresql.orgso that your      message can get through to the mailing list cleanly 


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments