Re: [GENERAL] Question on arrays

Steve Wolfe wrote:
>
>      Please forgive the question on such simple subjects...
>
>    The original idea I had was to make an array of text fields to store
> some data, but evidently text and varchar don't work in arrays.  I've
> tried char(1024)[] to no avail, about the only thing I can I can get to
> work it char16[] - but 16 characters just doesn't cut it. : )  The
> documentation on arrays was very limited (shoot, I couldn't even find
> char16 in the documentation), and tryiong to search usenet didn't turn
> much up.  Any pointers to where I can find more information?


If you need to store textfields like this, arrays of text are almost
certainly the wrong way to go about it. I don't think arrays on types
that do not have a fixed size are supported, and even if they were, this
would be horribly inefficient, as you could not easily calculate the
position of any element in the array.

Much better to have a key in your table, pointing to a second table
where you store the text fields.

table1:
    ....
    key   int4  default nextval('some-sequence')


table2:
    key    int4 default currval('some-sequence'),
    idx    int4,
    t      text



An easy way to get distinct keys is to use a sequence. As long as you
insert into table1 first, you will automatically get the correct key in
table2. Getting all or any specific field is now a simple join
operation.

Adriaan