From: | Decibel! <decibel(at)decibel(dot)org> |
---|---|
To: | Andreas <maps(dot)on(at)gmx(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to manage category-ids as array-fields ? |
Date: | 2008-06-21 02:52:55 |
Message-ID: | 94C4D62C-1E24-4391-B743-7B34F250A978@decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828
From | Date | Subject | |
---|---|---|---|
Next Message | Decibel! | 2008-06-21 02:54:14 | Re: Cross Tab Functions |
Previous Message | Scott Marlowe | 2008-06-20 16:22:23 | Re: "TZ"/"tz" not supported |