Thread: Some questions on using arrays.

Dear People

I'm finding Postgres very useful with its support for non-atomic
data-types.  I have a couple of questions about setting up and
querying arrays.

If this kind of thing is more for pgsql-novice, please let me know.

Questions refer to the following table.

create table bar ( id serial, links int4[] )

  id |  links
----+---------
   1 | {2,3,4}
   2 | {3,4,5}
   3 | {4,5,6}
   4 | {5,6,7}
   5 | {6}
   6 | {4,5,7}
   7 | {1,3,5}

1. How do I query the contents of an array?  For the example table I'd
    like to say something like 'select * from bar where links contains
    5', but operators like in, @ and ~ don't seem to work.

2. Imagine the table is a directed graph (e.g. a map of one-way
    streets between locations).  I'd like to have each element of links
    to be a reference to id (or a similar field in another table).

Any help or comments appreciated, thank you.

Ivan Uemlianin

On Thu, 26 Jul 2001, Ivan Uemlianin wrote:

> If this kind of thing is more for pgsql-novice, please let me know.
>
> Questions refer to the following table.
>
> create table bar ( id serial, links int4[] )
>
>   id |  links
> ----+---------
>    1 | {2,3,4}
>    2 | {3,4,5}
>    3 | {4,5,6}
>    4 | {5,6,7}
>    5 | {6}
>    6 | {4,5,7}
>    7 | {1,3,5}
>
> 1. How do I query the contents of an array?  For the example table I'd
>     like to say something like 'select * from bar where links contains
>     5', but operators like in, @ and ~ don't seem to work.

You probably want to look in contrib.  There's a bunch of array
functions and operators in there.

> 2. Imagine the table is a directed graph (e.g. a map of one-way
>     streets between locations).  I'd like to have each element of links
>     to be a reference to id (or a similar field in another table).

Well, you can make it store ids of the other field, you'd have to make
your own triggers to enforce integrity if you wanted it to be done by
the db.