Обсуждение: How to manage category-ids as array-fields ?

Hi,

I get from an outside source tables as simple textfiles to import.
Those are obviously results of views with joined tables.

Among the normal stuff are columns that have one or a list of id-numbers 
devided by a semicolon.
In the next column there is the corresponding text for this ids.

It looks like this:
17, ... ,  "1; 2;", "cat; mouse;", ...
23, ...,  "3;", "dog;", ...
42, ..., "2; 7;", "mouse; horse;", ...

Obviously the meaning is that some entity has those listed attributes. 
Most likely they are stored as a n:m-relation like:
17, 1
17, 2
23, 3
42, 2
42, 7

Is there a way to reproduce the output in the form above (as array) ?

Is there a way to import the data in the form above, so it gets neately 
stored in a n:m ?

Is it seen as a conceptual good solution to store such information 
within a text-column or array?
I'd rather doubt that PG would watch the integrity of those ids then.

On Jun 13, 2008, at 11:02 AM, Andreas wrote:
> I get from an outside source tables as simple textfiles to import.
> Those are obviously results of views with joined tables.
>
> Among the normal stuff are columns that have one or a list of id- 
> numbers devided by a semicolon.
> In the next column there is the corresponding text for this ids.
>
> It looks like this:
> 17, ... ,  "1; 2;", "cat; mouse;", ...
> 23, ...,  "3;", "dog;", ...
> 42, ..., "2; 7;", "mouse; horse;", ...
>
> Obviously the meaning is that some entity has those listed  
> attributes. Most likely they are stored as a n:m-relation like:
> 17, 1
> 17, 2
> 23, 3
> 42, 2
> 42, 7
>
> Is there a way to reproduce the output in the form above (as array) ?

SELECT array_to_string(array(1,2), '; ');

> Is there a way to import the data in the form above, so it gets  
> neately stored in a n:m ?

Well, you can easily turn it into an array:

SELECT string_to_array('1; 2', '; '); (You'll need to strip the  
trailing ;'s.

After than you can convert the array to a recordset if you want.  
There's some examples in the archives of how to do that (I think it's  
in the archives for -general; I know I was in one of the threads so  
searching for decibel might help narrow things down).

> Is it seen as a conceptual good solution to store such information  
> within a text-column or array?
> I'd rather doubt that PG would watch the integrity of those ids then.

I wouldn't do text. You could enforce some loose RI via triggers  
pretty easily if you used arrays.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828