pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan I search for an array in csf?
9+ messages / 4 participants
[nested] [flat]
* Can I search for an array in csf?
@ 2002-10-18 13:58 Vernon Wu <vernonw@gatewaytech.com>
2002-10-21 10:13 ` Re: Can I search for an array in csf? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Vernon Wu @ 2002-10-18 13:58 UTC (permalink / raw)
To: pgsql-sql
One field of a table stores an array of characters in a string fromat as "a,b,c,d". Is anyway to apply a select statement
without using stored procedure?
Thanks for your input.
Vernon
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can I search for an array in csf?
2002-10-18 13:58 Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
@ 2002-10-21 10:13 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2002-10-21 10:13 UTC (permalink / raw)
To: vernonw@gatewaytech.com; pgsql-sql
On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote:
> One field of a table stores an array of characters in a string fromat as
> "a,b,c,d". Is anyway to apply a select statement without using stored
> procedure?
>
> Thanks for your input.
Not really, and I can't think any way of accessing an index on this either.
Are you sure you wanted the characters stored this way? Even if the overhead
of a separate table isn't woth it, you might want to look into arrays and the
intarray stuff in contrib/
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: Re: Can I search for an array in csf?
@ 2002-10-22 02:55 Vernon Wu <vernonw@gatewaytech.com>
2002-10-22 03:02 ` Re: Fwd: Re: Can I search for an array in csf? Josh Berkus <josh@agliodbs.com>
2002-10-22 07:49 ` Re: Fwd: Re: Can I search for an array in csf? Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 9+ messages in thread
From: Vernon Wu @ 2002-10-22 02:55 UTC (permalink / raw)
To: pgsql-sql
Hi, Richard,
Thanks for your response and see below.
10/21/2002 3:13:57 AM, Richard Huxton <dev@archonet.com> wrote:
>On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote:
>> One field of a table stores an array of characters in a string fromat as
>> "a,b,c,d". Is anyway to apply a select statement without using stored
>> procedure?
>>
>> Thanks for your input.
>
>Not really, and I can't think any way of accessing an index on this either.
>Are you sure you wanted the characters stored this way? Even if the overhead
>of a separate table isn't woth it, you might want to look into arrays and the
>intarray stuff in contrib/
>
The reason I use this format for an array is that the array is dynamic. I have quite few cases of this type of situation. The
maximize length in some cases is known, is unknown in others. I have learnt the comment separated format is one way
to solve the problem. Someone also suggested to store the array as an object. I am not sure whether it works or not.
The application is written in Java, by the way.
I have taken a look at intarray by searching on the postgres.org web site as well as in google. (I use cypwin and unable
to find the contrib directory). My impression is it isn't a standard SQL data type. And its element is integer only.
It is my first time doing DB table design. Any helps will be gracfully appreciated.
Thanks,
Vernon
>--
> Richard Huxton
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: 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
>
-------- End of forwarded message --------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Re: Can I search for an array in csf?
2002-10-22 02:55 Fwd: Re: Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
@ 2002-10-22 03:02 ` Josh Berkus <josh@agliodbs.com>
2002-10-22 08:03 ` Re: Fwd: Re: Can I search for an array in csf? Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2002-10-22 03:02 UTC (permalink / raw)
To: vernonw@gatewaytech.com; pgsql-sql
Vernon,
> >> One field of a table stores an array of characters in a string fromat as
> >> "a,b,c,d". Is anyway to apply a select statement without using stored
> >> procedure?
> The reason I use this format for an array is that the array is dynamic. I
have quite few cases of this type of situation. The
> maximize length in some cases is known, is unknown in others. I have learnt
the comment separated format is one way
> to solve the problem. Someone also suggested to store the array as an
object. I am not sure whether it works or not.
> The application is written in Java, by the way.
You should store this data in a sub-table linked through a foriegn key.
Period. Messing with arrays will only lead you to heartache ...
Try the book "Database Design For Mere Mortals" for a primer on SQL DB design.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Re: Can I search for an array in csf?
2002-10-22 02:55 Fwd: Re: Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
2002-10-22 03:02 ` Re: Fwd: Re: Can I search for an array in csf? Josh Berkus <josh@agliodbs.com>
@ 2002-10-22 08:03 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-10-22 10:54 ` Re: Fwd: Re: Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
0 siblings, 1 reply; 9+ messages in thread
From: Achilleus Mantzios @ 2002-10-22 08:03 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Mon, 21 Oct 2002, Josh Berkus wrote:
>
> Vernon,
>
> > >> One field of a table stores an array of characters in a string fromat as
> > >> "a,b,c,d". Is anyway to apply a select statement without using stored
> > >> procedure?
>
> > The reason I use this format for an array is that the array is dynamic. I
> have quite few cases of this type of situation. The
> > maximize length in some cases is known, is unknown in others. I have learnt
> the comment separated format is one way
> > to solve the problem. Someone also suggested to store the array as an
> object. I am not sure whether it works or not.
> > The application is written in Java, by the way.
>
> You should store this data in a sub-table linked through a foriegn key.
> Period. Messing with arrays will only lead you to heartache ...
It depends.
I can tell you of situations that doing it with child tables
will hurt performance really bad.
Its just a matter of complexity.
One of the apps we run over here, deals with bunker
analysis of the vessels of our fleet.
For each vessel there are 4 formulas that describe the parameters of
the consumption of fuel oil under some given conditions.
I have implemented this using arrays.
The app is written in J2EE.
On a dual xeon 2.2 GHz with 1 GB for postgres,
it takes about 900 miliseconds to compute
some statistics (average, std deviation,etc..)
of the consumption of all vessels (about 20 of them)
for a period of 3 years (the values are stored for each day).
Before going with the formulas, we had a rather
primitive scheme originated from the previous
cobol application, based on subtable look ups,
(and there was no serious computations involved
just table lookups).
I can tell you the performance boost was remarkable.
>
> Try the book "Database Design For Mere Mortals" for a primer on SQL DB design.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Re: Can I search for an array in csf?
2002-10-22 02:55 Fwd: Re: Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
2002-10-22 03:02 ` Re: Fwd: Re: Can I search for an array in csf? Josh Berkus <josh@agliodbs.com>
2002-10-22 08:03 ` Re: Fwd: Re: Can I search for an array in csf? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2002-10-22 10:54 ` Vernon Wu <vernonw@gatewaytech.com>
0 siblings, 0 replies; 9+ messages in thread
From: Vernon Wu @ 2002-10-22 10:54 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Hi, Achilleus and Josh,
I know three ways to store dynamic array in DB: object, xml or csv, and sub-table. It seems to me there are some
problems of using the first method in Java. I don't know how the third method work out. That is the reason I use the
second method.
In my project, the simplest array type is an array of characters or user IDs. The complexest array is an array of data type
of userid and another text field. All operations on the arrays are simple: either element look up, add or delete an element.
Which method is the most suitable for those different operations.
Thanks very much for all your helps.
Vernon
Thanks for your information. See below.
10/22/2002 1:03:56 AM, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
>On Mon, 21 Oct 2002, Josh Berkus wrote:
>
>>
>> Vernon,
>>
>> > >> One field of a table stores an array of characters in a string fromat as
>> > >> "a,b,c,d". Is anyway to apply a select statement without using stored
>> > >> procedure?
>>
>> > The reason I use this format for an array is that the array is dynamic. I
>> have quite few cases of this type of situation. The
>> > maximize length in some cases is known, is unknown in others. I have learnt
>> the comment separated format is one way
>> > to solve the problem. Someone also suggested to store the array as an
>> object. I am not sure whether it works or not.
>> > The application is written in Java, by the way.
>>
>> You should store this data in a sub-table linked through a foriegn key.
>> Period. Messing with arrays will only lead you to heartache ...
>
>It depends.
>I can tell you of situations that doing it with child tables
>will hurt performance really bad.
>Its just a matter of complexity.
>
>One of the apps we run over here, deals with bunker
>analysis of the vessels of our fleet.
>
>For each vessel there are 4 formulas that describe the parameters of
>the consumption of fuel oil under some given conditions.
>
>I have implemented this using arrays.
>The app is written in J2EE.
>
>On a dual xeon 2.2 GHz with 1 GB for postgres,
>it takes about 900 miliseconds to compute
>some statistics (average, std deviation,etc..)
>of the consumption of all vessels (about 20 of them)
>for a period of 3 years (the values are stored for each day).
>
>Before going with the formulas, we had a rather
>primitive scheme originated from the previous
>cobol application, based on subtable look ups,
>(and there was no serious computations involved
>just table lookups).
>
>I can tell you the performance boost was remarkable.
>
>>
>> Try the book "Database Design For Mere Mortals" for a primer on SQL DB design.
>>
>> --
>> -Josh Berkus
>> Aglio Database Solutions
>> San Francisco
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/users-lounge/docs/faq.html
>>
>
>==================================================================
>Achilleus Mantzios
>S/W Engineer
>IT dept
>Dynacom Tankers Mngmt
>Nikis 4, Glyfada
>Athens 16610
>Greece
>tel: +30-10-8981112
>fax: +30-10-8981877
>email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Re: Can I search for an array in csf?
2002-10-22 02:55 Fwd: Re: Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
@ 2002-10-22 07:49 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 9+ messages in thread
From: Achilleus Mantzios @ 2002-10-22 07:49 UTC (permalink / raw)
To: Vernon Wu <vernonw@gatewaytech.com>; +Cc: pgsql-sql
On Mon, 21 Oct 2002, Vernon Wu wrote:
>
>
>
>
> Hi, Richard,
>
> Thanks for your response and see below.
>
> 10/21/2002 3:13:57 AM, Richard Huxton <dev@archonet.com> wrote:
>
> >On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote:
> >> One field of a table stores an array of characters in a string fromat as
> >> "a,b,c,d". Is anyway to apply a select statement without using stored
> >> procedure?
> >>
> >> Thanks for your input.
> >
> >Not really, and I can't think any way of accessing an index on this either.
> >Are you sure you wanted the characters stored this way? Even if the overhead
> >of a separate table isn't woth it, you might want to look into arrays and the
> >intarray stuff in contrib/
> >
>
> The reason I use this format for an array is that the array is dynamic. I have quite few cases of this type of situation. The
> maximize length in some cases is known, is unknown in others. I have learnt the comment separated format is one way
> to solve the problem. Someone also suggested to store the array as an object. I am not sure whether it works or not.
> The application is written in Java, by the way.
>
> I have taken a look at intarray by searching on the postgres.org web site as well as in google. (I use cypwin and unable
> to find the contrib directory). My impression is it isn't a standard SQL data type. And its element is integer only.
>
> It is my first time doing DB table design. Any helps will be gracfully appreciated.
Well some comments, since i have done a lot of work with
arrays, postgresql and java.
Arrays are supported by the postgresql jdbc driver just fine.
Arrays can be of any valid builtin or user defined type.
Postgresql Arrays along with intarray package are ideal for
doing small set manipulations, where the design fits
the natural model of your data (e.g. storing the factors
of a polynomial formula).
It would be nice if you had yourself a crash course on
relational db design.
Also i would advise you compiling and running postgres
on a unix system.
Note that in order to compile the intarray package
you need to have the sources installed.
>
> Thanks,
>
> Vernon
>
>
>
> >--
> > Richard Huxton
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: 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
> >
>
>
> -------- End of forwarded message --------
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can I search for an array in csf?
@ 2002-10-22 11:07 Vernon Wu <vernonw@gatewaytech.com>
0 siblings, 0 replies; 9+ messages in thread
From: Vernon Wu @ 2002-10-22 11:07 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
Hi, Christoph,
Thanks for reminding me regular expression.
The background of my question is about attributes of one element, say hobby for example. Different people have
different hobbies. Inside of the application, hobbies are denoted in various characters. The selection I mentioned in the
original mail refers to finding people who have certain hobbies in the case.
I think regular expression is the most effective way for the usage.
Thanks again.
Vernon
10/22/2002 5:50:22 AM, Christoph Haller <ch@rodos.fzk.de> wrote:
>>
>> I thought I had made my case clear. Let me rephrase it.
>>
>> I have a character array, {'a', 'b', 'c', 'd'} for example. And this
>array is stored in a DB table field as a string
>of "a,b,c,d".
>> Now, I want to find out whether the table field, or array, contains
>any character set of {'c', 'e', 'h'}. My question
> is
>> whether SQL statement is appliable for this selection, or the data has
>been retrieved and process in the application
>> level.
>>
>> I hope I make th case clear this time.
>>
>Ok, now I see.
>Maybe the Postgres POSIX Regular Expressions are what you are looking
>for.
>Suppose your array "a,b,c,d" is stored into a table field of type
>character,
>character varying or text.
>Then, if you are searching for all entries containing a 'c', 'e', or
>'h' character
>your SELECT statement would look like
>
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ '[ceh]' ;
>
>Still not sure if you mean a sequence like "c,e,h" on the other hand.
>Then
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ 'c,e,h' ;
>should do the trick - as long as the order of characters is identical.
>A sequence of "e,c,h" would not show up, of course.
>
>Regular Expressions are far more powerful than these two examples can
>show.
>Refer to the related chapter in the documentation.
>I hope this helps more than the other replies you've received telling
>you
>'Learn about DB-design first'.
>
>Regards, Christoph
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can I search for an array in csf?
@ 2002-10-22 16:16 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2002-10-22 16:16 UTC (permalink / raw)
To: vernonw@gatewaytech.com; +Cc: pgsql-sql
Vernon,
> Thanks Josh, I will take a look at this book.
>
> The only problem with the implemention I can see so far is
> performance: change format when read/write data. Other
> than that, I don't see a big problem. I, however, shall say the
> performance is important in my project.
I'd think Java would be able to do array --> table and table --> array
very quickly. Certainly, PHP and Perl do.
Also, you can use a custom aggregate to give yourself a comma-delimited
list from a subtable (see the article on custom aggregates at
techdocs.postgresql.org) However, custom aggregates are slow and
Java's array-handling is probably faster.
-Josh Berkus
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-10-22 16:16 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-18 13:58 Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
2002-10-21 10:13 ` Richard Huxton <dev@archonet.com>
2002-10-22 02:55 Fwd: Re: Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
2002-10-22 03:02 ` Josh Berkus <josh@agliodbs.com>
2002-10-22 08:03 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-10-22 10:54 ` Vernon Wu <vernonw@gatewaytech.com>
2002-10-22 07:49 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-10-22 11:07 Re: Can I search for an array in csf? Vernon Wu <vernonw@gatewaytech.com>
2002-10-22 16:16 Re: Can I search for an array in csf? Josh Berkus <josh@agliodbs.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