pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY to table with array columns (Longish)
16+ messages / 6 participants
[nested] [flat]
* COPY to table with array columns (Longish)
@ 2006-06-12 01:38 Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 14:42 ` Fwd: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
0 siblings, 2 replies; 16+ messages in thread
From: Phillip Smith @ 2006-06-12 01:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Fwd: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-06-12 14:42 ` Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 16+ messages in thread
From: Aaron Bono @ 2006-06-12 14:42 UTC (permalink / raw)
To: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-06-12 23:45 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Phillip Smith @ 2006-06-12 23:45 UTC (permalink / raw)
To: 'Aaron Bono' <aaron.bono@aranya.com>; +Cc: pgsql-sql
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
<mailto: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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-06-13 00:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2006-06-13 00:30 UTC (permalink / raw)
To: phillips@weatherbeeta.com.au; +Cc: 'Aaron Bono' <aaron.bono@aranya.com>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-13 02:58 ` Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Rod Taylor <pg@rbt.ca>
0 siblings, 2 replies; 16+ messages in thread
From: Aaron Bono @ 2006-06-13 02:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: phillips@weatherbeeta.com.au; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
@ 2006-06-13 03:18 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 16+ messages in thread
From: Phillip Smith @ 2006-06-13 03:18 UTC (permalink / raw)
To: 'Aaron Bono' <postgresql@aranya.com>; 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-06-13 04:11 ` Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
0 siblings, 1 reply; 16+ messages in thread
From: Aaron Bono @ 2006-06-13 04:11 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
@ 2006-06-13 05:29 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 15:05 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 18:21 ` Re: COPY to table with array columns (Longish) operationsengineer1@yahoo.com
0 siblings, 2 replies; 16+ messages in thread
From: Phillip Smith @ 2006-06-13 05:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-06-13 15:05 ` Aaron Bono <postgresql@aranya.com>
2006-06-13 23:22 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
1 sibling, 1 reply; 16+ messages in thread
From: Aaron Bono @ 2006-06-13 15:05 UTC (permalink / raw)
To: phillips@weatherbeeta.com.au; +Cc: pgsql-sql
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> |
> +-----------+---------+---------+---------+---------+
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 15:05 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
@ 2006-06-13 23:22 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-14 00:41 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 16+ messages in thread
From: Phillip Smith @ 2006-06-13 23:22 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 15:05 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 23:22 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-06-14 00:41 ` Aaron Bono <postgresql@aranya.com>
2006-06-14 00:51 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
0 siblings, 1 reply; 16+ messages in thread
From: Aaron Bono @ 2006-06-14 00:41 UTC (permalink / raw)
To: phillips@weatherbeeta.com.au; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 15:05 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 23:22 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-14 00:41 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
@ 2006-06-14 00:51 ` Phillip Smith <phillips@weatherbeeta.com.au>
0 siblings, 0 replies; 16+ messages in thread
From: Phillip Smith @ 2006-06-14 00:51 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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.org so 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-06-13 18:21 ` operationsengineer1@yahoo.com
2006-06-13 18:31 ` Re: COPY to table with array columns (Longish) operationsengineer1@yahoo.com
1 sibling, 1 reply; 16+ messages in thread
From: operationsengineer1@yahoo.com @ 2006-06-13 18:21 UTC (permalink / raw)
To: phillips@weatherbeeta.com.au; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 18:21 ` Re: COPY to table with array columns (Longish) operationsengineer1@yahoo.com
@ 2006-06-13 18:31 ` operationsengineer1@yahoo.com
0 siblings, 0 replies; 16+ messages in thread
From: operationsengineer1@yahoo.com @ 2006-06-13 18:31 UTC (permalink / raw)
To: operationsengineer1@yahoo.com; phillips@weatherbeeta.com.au; pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
@ 2006-06-13 03:18 ` Rod Taylor <pg@rbt.ca>
2006-06-13 03:51 ` Re: COPY to table with array columns (Longish) Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 1 reply; 16+ messages in thread
From: Rod Taylor @ 2006-06-13 03:18 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: phillips@weatherbeeta.com.au; pgsql-sql
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
>
--
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COPY to table with array columns (Longish)
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 23:45 ` Re: COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Re: COPY to table with array columns (Longish) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Re: COPY to table with array columns (Longish) Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Re: COPY to table with array columns (Longish) Rod Taylor <pg@rbt.ca>
@ 2006-06-13 03:51 ` Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 16+ messages in thread
From: Richard Broersma Jr @ 2006-06-13 03:51 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; Aaron Bono <postgresql@aranya.com>; +Cc: phillips@weatherbeeta.com.au; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2006-06-14 00:51 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-12 01:38 COPY to table with array columns (Longish) Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-12 14:42 ` Aaron Bono <postgresql@aranya.com>
2006-06-12 23:45 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 00:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-13 02:58 ` Aaron Bono <postgresql@aranya.com>
2006-06-13 03:18 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 04:11 ` Aaron Bono <postgresql@aranya.com>
2006-06-13 05:29 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 15:05 ` Aaron Bono <postgresql@aranya.com>
2006-06-13 23:22 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-14 00:41 ` Aaron Bono <postgresql@aranya.com>
2006-06-14 00:51 ` Phillip Smith <phillips@weatherbeeta.com.au>
2006-06-13 18:21 ` operationsengineer1@yahoo.com
2006-06-13 18:31 ` operationsengineer1@yahoo.com
2006-06-13 03:18 ` Rod Taylor <pg@rbt.ca>
2006-06-13 03:51 ` Richard Broersma Jr <rabroersma@yahoo.com>
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