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 [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
