Thread: Join a varchar array

Hi,

I have a field in which I save the follow:
{{8,0.58},{9,972420},{10,239544},{6,0.49},{7,0.63}}

The first field is a reference to an id in another table and the second
field is a value.

Can these values be used somehow in a select query to join the other
table?

I don't mind getting them on different rows but I would prefer to
get them back as an array.


regards
Robin

Robin Ericsson wrote:
> Hi,
>
> I have a field in which I save the follow:
> {{8,0.58},{9,972420},{10,239544},{6,0.49},{7,0.63}}
>
> The first field is a reference to an id in another table and the second
> field is a value.
>
> Can these values be used somehow in a select query to join the other
> table?

You appear to be using the array as a replacement for a table. Don't do
that. From the little information you've provided, it looks like you
want a separate table (a_ref, b_ref, float_val) where a_ref references
the current table, b_ref the other table.

--
   Richard Huxton
   Archonet Ltd

On Thu, 2004-06-17 at 11:38, Richard Huxton wrote:
> Robin Ericsson wrote:
> > Hi,
> >
> > I have a field in which I save the follow:
> > {{8,0.58},{9,972420},{10,239544},{6,0.49},{7,0.63}}
> >
> > The first field is a reference to an id in another table and the second
> > field is a value.
> >
> > Can these values be used somehow in a select query to join the other
> > table?
>
> You appear to be using the array as a replacement for a table. Don't do
> that. From the little information you've provided, it looks like you
> want a separate table (a_ref, b_ref, float_val) where a_ref references
> the current table, b_ref the other table.

Thats correct, I choose this way because there will be a lot of entries
and it will grow pretty quickly, but maybe postgres will be able to
handle this?

If I went for a separate table it will contain over 3 milj. entries
within in a couple of weeks and still grow after that but not at the
same speed as the first weeks.



regards
Robin

Robin Ericsson wrote:
> On Thu, 2004-06-17 at 11:38, Richard Huxton wrote:
>>
>>You appear to be using the array as a replacement for a table. Don't do
>>that. From the little information you've provided, it looks like you
>>want a separate table (a_ref, b_ref, float_val) where a_ref references
>>the current table, b_ref the other table.
>
>
> Thats correct, I choose this way because there will be a lot of entries
> and it will grow pretty quickly, but maybe postgres will be able to
> handle this?

If it can't handle the data in tables, why should it handle it in
arrays? You're using a relational database, stick to relations.

> If I went for a separate table it will contain over 3 milj. entries
> within in a couple of weeks and still grow after that but not at the
> same speed as the first weeks.

There are people here with hundreds of millions of rows in some tables.
Keep your design simple and clean, apply indexes once you have some idea
of what activity your system will see and subscribe to the performance list.

PG has some odd corner-cases where performance could be better, but on
the whole it does an excellent job of coping with large numbers of users
and large amounts of data.

--
   Richard Huxton
   Archonet Ltd